Search code examples
rubypostgresqlsinatra

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

So I am trying to add top posts functionality for my Sinatra project using postgresql with the following statement Like.group(:exercise_id).order('COUNT(exercise_id) DESC').each do |like|

But when I try this I get an error saying column "likes.id" must appear in the GROUP BY clause or be used in an aggregate function

And when I add this Like.group(:exercise_id, :id).order('COUNT(exercise_id) DESC')

It gives me the whole table when all I want is to group by likes. Is there a workaround for this?

Solution

  • What the error is telling you, is that you're selecting for "likes.id", but there is no single likes.id that the database can give you.

    This is because by default when you don't tell Rails what you need, Rails will select EVERYTHING.

    Think, do you actually need "likes.id"? Looks to me that you're grouping by exercise_id so what you're trying to get is exercises and their like counts. (correct me if I'm wrong.) You don't actually need specific like id-s.

    If that's so, we need to tell rails about our intention.

    Like.group(:exercise_id).order('COUNT(exercise_id) DESC').select(:exercise_id)

    If you also need the count itself, just add it to the select.

    Something else you might want to try is just .count. It's pretty smart and will respect your grouping. See if this helps.

    Like.group(:exercise_id).count