Chaining relations break?

Rails
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

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
5

On Wednesday, 4 December 2013 19:00:31 UTC-5, Ruby-Forum.com User wrote:

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

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

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