chaining relations break?

[Fearless Fool]

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...

--
Posted via http://www.ruby-forum.com/.

[Fearless Fool]

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().

[Fearless Fool]

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.

[Matt Jones]

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 

[Fearless Fool]

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