使用 Arel 按频率对记录排序

发布于 2024-12-06 14:49:35 字数 848 浏览 0 评论 0原文

如何在 Arel 中检索按计数排序的一组记录？我有一个模型可以跟踪产品获得的浏览量。我想找到过去 Y 天内最常查看的 X 个产品。

这个问题是在从 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)
}

这是我到目前为止所得到的：

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

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

更新我的想法是：

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

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

原文

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.

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.

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.

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).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

冰火雁神 2024-12-13 14:49:35

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

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

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

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

回复收藏 0 原文

聚集的泪 2024-12-13 14:49:35

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;

从您的示例推断，它应该是：

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

_{免责声明：Ruby 语法对我来说是一门外语。}

SQL would be:

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.}

回复收藏 0 原文

~没有更多了~