Converting Rails ActiveRecord GROUP query from MySQL to PostgreSQL
===

I have a ActiveRecord query I’ve been working with (history [here][1]) that works great in MySQL, but now I need to convert my database to PostgreSQL and I'm getting an error. Here is the query:

    class Shoe < ActiveRecord::Base
      has_many :purchases
      
      def self.available_shoes
        #show all shoes that have been purchased less than num_in_stock
        num_in_stock = 3
        Shoe.includes(:purchases)
            .group("purchases.shoe_id")
            .having("COUNT(purchases.shoe_id) < ?", num_in_stock)
      end
    end


Simply switching the gems and adapters to postgres is not enough: I now get the following error:
> ActionView::Template::Error (PG::Error: ERROR:  column "shoes.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "shoes"."id" AS t0_r0, "shoes"."created_at" AS ...

I tried changing

    .group("purchases.shoe_id")
to
    
    .group("purchases.shoe_id, shoes.id, purchases.id")
and while this got rid of the error, it also changed the sql and broke some of my tests. 

I have read many stackoverflow questions regarding this error but I was unable to find a solution. What do I need to change in my ActiveRecord query to make this work in postgres?

Thanks in advance!


  [1]: http://stackoverflow.com/questions/11406221/how-to-adjust-activerecord-or-straight-sql-query-to-include-zero-count-records