Associated activerecords with aggregate functions (sum, avg, ...) with a limit

Question

Given this pseudo query:

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

Here we have a problem that the sum received is not "limited" by the value of X (instead, it sums all the values ​​on the column that the sum of the records is X (first)).

It seems that limit is taken after calculating the cumulative sum function.

How can I get the amount limited only by the first X-elements?

Answer 1

The MurifoX clause does not work because LIMIT applies only to the final result set, which has only one row (sum). See this thread .

The easiest way to solve this is not to do an account in the database:

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

Loads column values ​​into Ruby and sums them there. Now, if the record set is really huge, it will be noticeably less efficient, just because all of these values ​​are loaded into your application memory.

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

The suggested method for doing this in SQL uses a subquery, for example:

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

There is really no simple or easy way to turn this into an ActiveRecord query, as they are awaiting selection from a particular model table.

Answer 2

Did you try to write the request differently?

 My_model.where(:id => id).order('date DESC').limit(X).sum(:column) 

This way you filtered your model to summarize the narrowed values.