Bound activerecords-queries using aggregate funcs (sum, avg,...) with limit

Question

Given this pseudo-query:

Model.sum(:column, :condition => ['id = ?', id], order => "date DESC", :limit => X)

Here we have the problem that the sum obtained is not "limited" by the value X (it takes as sum all values on the column instead that the sum of the X (firsts) entries).

It seems that the limit is taken after calculating the aggregate func sum.

How can I get the sum only limited by the firsts X entries?

Answer 1

MurifoX's suggestion doesn't work because LIMIT applies only to the final result set, which has only one row (the sum). See this thread.

The simplest way to solve this is not to do the count in the database:

Model.where(:id => id).order('date DESC').limit(3).pluck(:column).compact.sum

This loads the values of column into Ruby and sums it there. Now, if the set of records is really enormous, this will be observably less efficient, just because all those values are getting loaded into your app's memory.

Edit: Added .compact - this will remove nils from the array before summing it (which causes an error).

The suggested method of doing this in SQL is using a subquery, like so:

SELECT SUM(subquery.column) FROM (
  SELECT * FROM models WHERE id = 5 LIMIT 3
) AS subquery;

There's not really an easy or straightforward way to turn this into an ActiveRecord query, since those expect to be selecting from a particular model's table.