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/.
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/0626dafcf8c15fad8955eff979f40a01%40ruby-forum.com.
For more options, visit https://groups.google.com/groups/opt_out.
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().
--
Posted via http://www.ruby-forum.com/.
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/e027d962ab05ca67ffbd7c81ce820dea%40ruby-forum.com.
For more options, visit https://groups.google.com/groups/opt_out.
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.
--
Posted via http://www.ruby-forum.com/.
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/9e71aacaa8c39b5631efe9aa48fc3563%40ruby-forum.com.
For more options, visit https://groups.google.com/groups/opt_out.
On Wednesday, 4 December 2013 19:00:31 UTC-5, Ruby-Forum.com User wrote:> > 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. > > -- > 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 Jones -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/11bc5cd1-0594-4fef-8e33-7f8aae0432fb%40googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
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 -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/35f183fb85fb45b7d7c3e1e8aad03da3%40ruby-forum.com. For more options, visit https://groups.google.com/groups/opt_out.