# chaining relations break?

**URL:** <https://discuss.rubyonrails.org/t/chaining-relations-break/68206>\
**Category:** rubyonrails-talk\
**Created:** [December 3, 2013, 9:28pm UTC](https://discuss.rubyonrails.org/t/chaining-relations-break/68206 "2013-12-03T21:28:58Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![11155](https://avatars.discourse-cdn.com/v4/letter/1/d26b3c/32.png) [@11155](https://discuss.rubyonrails.org/u/11155)\
**Post date:** [December 3, 2013, 9:28pm UTC](https://discuss.rubyonrails.org/t/chaining-relations-break/68206/1 "2013-12-03T21:28:58Z")

</div>

The following works:

&nbsp;&nbsp;\>\> r = u.utility\_data.select("utility\_id, sum(ami\_residential)").group("utility\_id") &nbsp;&nbsp;=\> #\<ActiveRecord::Relation [#\<UtilityDatum id: nil, utility\_id: 5621\>]\> &nbsp;&nbsp;\>\> r.first[:sum] &nbsp;&nbsp;=\> 263

but when written as a one-liner:

&nbsp;&nbsp;\>\> 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) &nbsp;&nbsp;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) &nbsp;&nbsp;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...

---

<div class="post-metadata">

**Author:** ![11155](https://avatars.discourse-cdn.com/v4/letter/1/d26b3c/32.png) [@11155](https://discuss.rubyonrails.org/u/11155)\
**Post date:** [December 3, 2013, 9:37pm UTC](https://discuss.rubyonrails.org/t/chaining-relations-break/68206/2 "2013-12-03T21:37:54Z")

</div>

BTW, I also tried this:

UtilityDatum. &nbsp;&nbsp;select("sum(ami\_residential)"). &nbsp;&nbsp;where(:utility =\> u). &nbsp;&nbsp;group("utility\_id"). &nbsp;&nbsp;unscope(:order, :limit). &nbsp;&nbsp;first

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

---

<div class="post-metadata">

**Author:** ![11155](https://avatars.discourse-cdn.com/v4/letter/1/d26b3c/32.png) [@11155](https://discuss.rubyonrails.org/u/11155)\
**Post date:** [December 5, 2013, 12:00am UTC](https://discuss.rubyonrails.org/t/chaining-relations-break/68206/3 "2013-12-05T00:00:31Z")

</div>

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

UtilityDatum. &nbsp;&nbsp;select("sum(ami\_residential)"). &nbsp;&nbsp;where(:utility =\> u). &nbsp;&nbsp;group("utility\_id"). &nbsp;&nbsp;reorder(''). &nbsp;&nbsp;first

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

---

<div class="post-metadata">

**Author:** ![Matt\_Jones](https://avatars.discourse-cdn.com/v4/letter/m/258eb7/32.png) [@Matt\_Jones](https://discuss.rubyonrails.org/u/Matt_Jones)\
**Post date:** [December 5, 2013, 12:44pm UTC](https://discuss.rubyonrails.org/t/chaining-relations-break/68206/4 "2013-12-05T12:44:56Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![11155](https://avatars.discourse-cdn.com/v4/letter/1/d26b3c/32.png) [@11155](https://discuss.rubyonrails.org/u/11155)\
**Post date:** [December 5, 2013, 3:01pm UTC](https://discuss.rubyonrails.org/t/chaining-relations-break/68206/5 "2013-12-05T15:01:21Z")

</div>

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