GroupingError: ERROR: column must appear in the GR

问题:

I have code in my controller that is ranking albums by the highest average review rating (used code from this solution How to display highest rated albums through a has_many reviews relationship):

@albums = Album.joins(:reviews).select("*, avg(reviews.rating) as average_rating").group("albums.id").order("average_rating DESC")

This code works perfectly in my development environment (sqlite3), however when I pushed the code to heroku and to postgresql I got this error:

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

I realize this is a fairly common problem, I am a bit inexperienced with SQL so I am having trouble refactoring the code so it will work in both my development and production environments.

回答1:

You are not allowed to select reviews.id (selected implicitly through the wildcard *) without adding it to the GROUP BY clause or applying an aggregate function like avg(). The solution is to do one of the following:

1. Remove the wildcard * from your select
2. Add the field reviews.id to your group clause
3. Select reviews.id explicitly and apply an aggregate function to it (e.g. sum(reviews.id))
4. Replace the wildcard * with the table-specific wildcard albums.*

The second and third option do not make much sense in your scenario though. Based on your comment, I added option four.

回答2:

Just would like to share this code on ruby using active record (sinatra)

I had to add "group by" to an "order by" function, so line of code ...

from:

@models = Port.all.order('number asc')

to:

@models = Port.select(:id, :device_id, :number, :value, :sensor, :UOM).all.order('number asc').group(:id,:sensor,:UOM)

and it worked perfect, just remember the ID field in this case "Port.id" must be added to the group clause otherwise will raise this error, and as @slash mentioned you can not achieve this with special functions (select implicitly through the wildcard * or in my case using "all")

回答3:

The only really acceptable solution I found to this kind of problem was with this code:

@albums = Album.joins(:reviews).select("*, avg(reviews.rating) as average_rating").group_by(&:id).order("average_rating DESC")

I hope it helps someone.