Rails PG GroupingError column must appear in the GROUP BY clause

Question

there are a few topics about this already with accepted answers but I couldn't figure out a solution based on those: Eg:

Ruby on Rails: must appear in the GROUP BY clause or be used in an aggregate function
GroupingError: ERROR: column must appear in the GROUP BY clause or be used in an aggregate function
PGError: ERROR: column "p.name" must appear in the GROUP BY clause or be used in an aggregate function

My query is:

Idea.unscoped.joins('inner join likes on ideas.id = likes.likeable_id').
select('likes.id, COUNT(*) AS like_count, ideas.id, ideas.title, ideas.intro, likeable_id').
group('likeable_id').
order('like_count DESC')

This is fine in development with sqlite but breaks on heroku with PostgreSQL.

The error is:

PG::GroupingError: ERROR:  column "likes.id" must appear in the GROUP BY clause or be used in an aggregate function

If I put likes.id in my group by then the results make no sense. Tried to put group before select but doesn't help. I even tried to take the query into two parts. No joy. :(

Any suggestions appreciated. TIA!

SHS SHS · Accepted Answer · 2015-02-19T11:36:41

I don't know why you want to select likes.id in the first place. I see that you basically want the like_count for each Idea; I don't see the point in selecting likes.id. Also, when you already have the ideas.id, I don't see why you would want to get the value of likes.likeable_id since they'll both be equal. :/

Anyway, the problem is since you're grouping by likeable_id (basically ideas.id), you can't "select" likes.id since they would be "lost" by the grouping.

I suppose SQLite is lax about this. I imagine it wouldn't group things properly.

ANYWAY(2) =>

Let me propose a cleaner solution.

# model
class Idea < ActiveRecord::Base
  # to save you the effort of specifying the join-conditions
  has_many :likes, foreign_key: :likeable_id
end

# in your code elsewhere
ideas = \
  Idea.
  joins(:likes).
  group("ideas.id").
  select("COUNT(likes.id) AS like_count, ideas.id, ideas.title, ideas.intro").
  order("like_count DESC")

If you still want to get the IDs of likes for each item, then after the above, here's what you could do:

grouped_like_ids = \
  Like.
  select(:id, :likeable_id).
  each_with_object({}) do |like, hash|
    (hash[like.likeable_id] ||= []) << like.id
  end

ideas.each do |idea|
  # selected previously:
  idea.like_count
  idea.id
  idea.title
  idea.intro

  # from the hash
  like_ids = grouped_like_ids[idea.id] || []
end

Other readers: I'd be very interested in a "clean" one-query non-sub-query solution. Let me know in the comments if you leave a response. Thanks.

Rails PG GroupingError column must appear in the GROUP BY clause

1 Answers