chaining relations break?

rubyonrails-talk
1

The following works:

  >> r = u.utility_data.select("utility_id, sum(ami_residential)").group("utility_id")   => #<ActiveRecord::Relation [#<UtilityDatum id: nil, utility_id: 5621>]>   >> r.first[:sum]   => 263

but when written as a one-liner:

  >> u.utility_data.select("utility_id, sum(ami_residential)").group("utility_id").first[:sum]

it fails with "PG::GroupingError: ERROR: column "utility_data.id" must appear in the GROUP BY clause or be used in an aggregate function" I THINK what's happening is that the working version generates the following SQL:

SELECT utility_id, sum(ami_residential)   FROM "utility_data" WHERE "utility_data"."utility_id" = $1 GROUP BY utility_id [["utility_id", 5621]]

whereas the failing version adds "ORDER BY" and "LIMIT" to the query:

SELECT utility_id, sum(ami_residential)   FROM "utility_data" WHERE "utility_data"."utility_id" = $1 GROUP BY utility_id ORDER BY "utility_data"."id" ASC LIMIT 1 [["utility_id", 5621]]

which triggers the grumpy ol' postgresql error.

So two questions:

- is this expected behavior? - is there a way to inhibit the addition of ORDER BY and LIMIT?

Thanks...

2

BTW, I also tried this:

UtilityDatum.   select("sum(ami_residential)").   where(:utility => u).   group("utility_id").   unscope(:order, :limit).   first

... but that still tacks on ORDER BY and LIMIT clauses to the query, so it still fails. Evidently I don't understand unscope().

3

Solved (though I'm not entirely sure why this works):

UtilityDatum.   select("sum(ami_residential)").   where(:utility => u).   group("utility_id").   reorder('').   first

The reorder() prevents the ORDER BY clause from being emitted, so the generated SQL is valid.

4

The ‘ORDER BY’ and ‘LIMIT’ clauses are actually coming from the first call on the end of the chain.

For what it’s worth, if the first query you mentioned is the one you want you may want to check out ActiveRecord::Calculations, in particular sum:

http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i-sum

Your code would look like:

r = u.utility_data.group(:utility_id).sum(:ami_residential)

At this point, r is a Hash:

{ 42 => 127.25, 76 => 321.02, etc }

of utility_id => sum pairs.

If you just want data rolled up for one Utility (I’m guessing that’s what u in the code above is…) you could skip the group:

sum = u.utility_data.sum(:ami_residential)

This returns a single number, the sum for all the related records.

–Matt Jones

5

Matt:

If you just want data rolled up for *one* Utility (I'm guessing that's what `u` in the code above is...) you could skip the group:

sum = u.utility_data.sum(:ami_residential)

You are spot on: in this case I'm just considering one Utility at a time. I don't know what possessed me to include the GROUP clause since the WHERE clause alone is sufficient. Time to go back to SQL camp...

Many thanks.

- ff