CODEWITHSUNDEEP
postgresqlruby-on-rails-3heroku
The Pied Pipes
The Pied Pipes

Reputation: 1435

Heroku PostgreSQL GROUP_BY error in Rails app

I've got a rails app that works fine in development (SQLite) but is throwing lots of errors when I've deployed it via Heroku, which uses PostgreSQL I gather.

the error message getting 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):

this is being thrown 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'd really like to keep the code clean so it works on both development and production DBs...can anyone shed some light?

I have 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.

Upvotes: 4

Views: 1298

Answers (3)

grosser
grosser

Reputation: 15097

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

Upvotes: 0

Paul Ketelle
Paul Ketelle

Reputation: 51

Practice.group(Practice.col_list)



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

Upvotes: 5

Peter Eisentraut
Peter Eisentraut

Reputation: 36739

You need to group by all the columns in the select list, so

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

Your idea to group only by id is sound (assuming id is the primary key), but PostgreSQL will only support that in version 9.1.

Upvotes: 4

Related Questions