用Arel按频率订购记录

How do I retrieve a set of records, ordered by count in Arel? I have a model which tracks how many views a product get. I want to find the X most frequently viewed products over the last Y days.

我如何检索一组记录，在Arel中排序?我有一个模型，它可以跟踪一个产品获得了多少视图。我想找出过去Y天里被浏览次数最多的产品。

This problem has cropped up while migrating to PostgreSQL from MySQL, due to MySQL being a bit forgiving in what it will accept. This code, from the View model, works with MySQL, but not PostgreSQL due to non-aggregated columns being included in the output.

这个问题在从MySQL迁移到PostgreSQL的过程中出现了，因为MySQL对它所接受的内容有点宽容。从视图模型来看，这段代码可以使用MySQL，但是由于输出中包含了非聚合列，所以不能使用PostgreSQL。

scope :popular, lambda { |time_ago, freq|
  where("created_on > ?", time_ago).group('product_id').
    order('count(*) desc').limit(freq).includes(:product)
}

Here's what I've got so far:

以下是我目前所得到的:

View.select("id, count(id) as freq").where('created_on > ?', 5.days.ago).
  order('freq').group('id').limit(5)

However, this returns the single ID of the model, not the actual model.

但是，这将返回模型的单个ID，而不是实际的模型。

Update I went with:

我和更新:

select("product_id, count(id) as freq").
  where('created_on > ?', time_ago).
  order('freq desc').
  group('product_id').
  limit(freq)

On reflection, it's not really logical to expect a complete model when the results are made up of GROUP BY and aggregate functions results, as returned data will (most likely) match no actual model (row).

根据反射，当结果由GROUP BY和聚合函数结果组成时，期望一个完整的模型是不符合逻辑的，因为返回的数据(很可能)与实际的模型(行)不匹配。

2 个解决方案

#1

you have to extend your select clause with all column you wish to retrieve. or

您必须使用希望检索的所有列扩展select子句。或

select("views.*, count(id) as freq")

#2

SQL would be:

SQL将:

SELECT product_id, product, count(*) as freq
 WHERE created_on > '$5_days_ago'::timestamp
 GROUP BY product_id, product
 ORDER BY count(*) DESC, product
 LIMIT 5;

Extrapolating from your example, it should be:

从你的例子推断，应该是:

View.select("product_id, product, count(*) as freq").where('created_on > ?', 5.days.ago).
  order("count(*) DESC" ).group('product_id, product').limit(5)

_{Disclaimer: Ruby syntax is a foreign language to me.}

声明:Ruby语法对我来说是一门外语。

#1