Giuseppe - 2 months ago 5x

Ruby Question

I have a

`Game`

`points`

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`

Thanks

Answer

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)
Game.from(sq).sum('"points"')
```

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.

Source (Stackoverflow)

Comments