Heroku PostgreSQL GROUP_BY Error in Rails Application

Question

I have a rails application that works fine in development (SQLite), but throws a lot of errors when I deployed it through Heroku, which uses PostgreSQL, which I am building.

error message returned:

ActionView::Template::Error (PGError: ERROR: column "practices.id" must appear in the GROUP BY clause or be used in an aggregate function: SELECT "practices".* FROM "practices" WHERE ("practices".activity_id = 1) AND ("practices"."created_at" BETWEEN '2011-01-01' AND '2011-01-31') GROUP BY DATE(created_at) ORDER BY created_at DESC): 

it throws when I call the following:

 def month_days_not_practiced(date = Date.today) p = practices.where(:created_at => date.at_beginning_of_month..date.at_end_of_month).group("DATE(created_at)").to_a.count days_in_month(date.year, date.month) - p end 

I would very much like the code to be clean, so it works with both development bases and production ... can anyone shed some light?

I tried this:

 def month_days_not_practiced(date = Date.today) p = practices.where(:created_at => date.at_beginning_of_month..date.at_end_of_month).group("practices.id, DATE(created_at)").to_a.count days_in_month(date.year, date.month) - p end 

to no avail ...

TIA.

Answer 1

You need to group all the columns in the selection list, so

 .group("practices.id, practices.foo, practices.bar, ..., DATE(created_at)") 

Your idea of ​​grouping only with id sounds (assuming id is the primary key), but PostgreSQL will only support version 9.1.

Answer 2

 Practice.group(Practice.col_list) def self.col_list Practice.column_names.collect {|c| "practices.#{c}"}.join(",") end 

Answer 3

 Practice.joins(:foobar).group_all def self.group_all group(group_all_columns) end def self.group_all_columns @group_all_columns ||= column_names.collect {|c| "#{table_name}.#{c}"}.join(",") end