问题描述
我有一个 Product 并且每个产品都有_many ratings.我正在尝试创建一个 :highest_rated 产品范围，它按产品的最高平均评分(每个评分都在 ratings 表中)对产品进行排序.我试过这个:
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')
但这给了我一个 misuse of aggregate: avg() 错误.
But that gave me a misuse of aggregate: avg() error.
有关如何按最高平均评分订购我的产品的任何提示?
Any tips on how to order my products by their highest average rating?
推荐答案
这有效:
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')
以上在 PostgreSQL 中不起作用.我改用了这个(它在 SQLite 和 PostgreSQL 中都有效):
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')
这篇关于:highest_rated 范围按平均评分排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！