:highest_rated scope to order by average rating

问题:

I have a Product and each product has_many ratings. I'm trying to create a :highest_rated Product scope which orders the products by their highest average rating (each rating is in the ratings table). I tried this:

scope :highest_rated, includes(:ratings).order('avg(ratings.rating) DESC')

But that gave me a misuse of aggregate: avg() error.

Any tips on how to order my products by their highest average rating?

回答1:

This works:

scope :highest_rated, includes(:ratings).group('product_id').order('AVG(ratings.rating) DESC')

To only fetch the products with an existing rating:

scope :highest_rated, includes(:ratings).group('product_id').where('ratings.rating IS NOT NULL').order('AVG(ratings.rating) DESC')

EDIT: the above won't work in PostgreSQL. I used this instead (which works in both SQLite and PostgreSQL):

scope :highest_rated, where("products.id in (select product_id from ratings)").group('products.id, products.name, products.all_other_fields').joins(:ratings).order('AVG(ratings.rating) DESC')