:highest_rated 范围按平均评分排序 [英] :highest_rated scope to order by average rating

问题描述

我有一个 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 范围按平均评分排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！