[SOLVED] PGError: ERROR: column “recipes.id” must appear in the GROUP BY clause or be used in an aggregate function

PGError: ERROR: column “recipes.id” must appear in the GROUP BY clause or be used in an aggregate function

I have this SQL query in my DB that is causing a problem with PostgreSQL on Heroku. It is causing the page not to load with the above error in the Heroku logs. I am using PostgreSQL 9.1.6 so previous bugs have apparently been fixed:

def self.top_countries
joins(:recipes).
  select('countries.*, count(*) AS recipes_count').
  group('countries.id').
  order('recipes_count DESC')
end

How can I refactor this so that it will work?

Solution

def self.top_countries
joins(:recipes).
  select('countries.id, count(*) AS recipes_count').
  group('countries.id').
  order('recipes_count DESC')

This generates the SQL

select countries.id, count(*) AS recipes_count
  from countries
  join recipes on countries.id = recipes.country_id
 group by countries.id
 order by recipes_count

You'll notice that you only have 2 columns in the SELECT.
Not being a Heroku expert, I suspect you can get it to work by explicitly listing all column that you need from countries, and grouping by the full column list i.e.

def self.top_countries
joins(:recipes).
  select('countries.id, countries.name, countries.other, count(*) AS recipes_count').
  group('countries.id, countries.name, countries.other').
  order('recipes_count DESC')

There might be a more concise way to join the original answer (top part) with another join to top_countries on countries.id to get the rest of the columns after the group by.