Giuseppe Giuseppe -4 years ago 72
Ruby Question

ActiveRecord calculations on subsets or records

I have a

model with a
attribute, and I want to compute the sum of the top 20 scores.

I can pluck the points and have ruby compute the sum, as in:

Game.order('points desc').limit(20).pluck(:points).sum

But I am curious as to whether there is a straightforward way to have AR produce an SQL aggregate calculation to accomplish the same. The following naive attempt does not work:

Game.sum(:points, order: 'points desc', limit: 20)
SELECT SUM(`games`.`points`) FROM `games`


Answer Source

I am going to answer my own question, but will gladly award a better solution.

The sum function really only takes one argument, and while AR 4.x used to simply ignore any extra args, the new AR 5.0.0 raises an error. The original attempt was indeed naive.

Following @pitabas' suggestion, I then tried:

Game.order('points desc').limit(20).sum(:points)
SELECT SUM("games"."points") FROM "games" LIMIT ?  [["LIMIT", 20]]

As you can tell from the SQL constructed by AR, this does not work either, because the LIMIT clause acts on the result of the query, which is a single value. Furthermore, the ORDER clause is ignored altogether. The result of the above expression is the total sum of the points column, for all records.

An SQL query that actually works is something like:

SELECT SUM(points) FROM (SELECT points FROM games ORDER BY points DESC LIMIT 20)

The closest I got to that is the following:

sq = Game.order('points desc').limit(20)

which yields:

SELECT SUM("points") FROM (SELECT  "games".* FROM "papers" ORDER BY points desc LIMIT ?) subquery  [["LIMIT", 20]]

Hack: note the double set of quotes around the points. That's how I managed to enforce a literal interpretation of the sum argument. Without them, the query raises a "no such column" error.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download