CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecordgroup-byarel
dkam
dkam

Reputation: 3916

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

Upvotes: 3

Views: 928

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

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.

Upvotes: 1

Marian Theisen
Marian Theisen

Reputation: 6353

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

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

Upvotes: 1

Related Questions