Ordering records by frequency with Arel

Question

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

Answer 1

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

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