开发者

Ordering records by frequency with 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.

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').limi开发者_运维技巧t(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).


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

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


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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜