I have 2 models:
class Operation < ActiveRecord::Base
belongs_to :client
*default_scope* order: ''operations.value_date DESC''
end
class Client < ActiveRecord::Base
has_many :operations, dependent: :destroy
* default_scope* order: ''clients.lastname''
end
Is there a more elegant way to get all the operations grouped by client
with total sum calculated what is done as follows:
@operations =
Operation.unscoped.includes(:client).order("clients.lastname").select("client_id,
sum(total) as total").group("client_id").paginate(page:
params[:page])
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-talk/-/Yr-_HwaLYzcJ.
For more options, visit https://groups.google.com/groups/opt_out.
Even if the below solutions I found work in the console, it is no the case
in the controller because the result is no more Array or AR relation but
ActiveSupport::OrderedHash and ''paginate'' method does not
accept that:
*1st way:*
irb(main):019:0> cc =
Operation.includes(:client).group(''operations.client_id'').limit(3).sum(''operations.total'')
(0.0ms) SELECT SUM(operations.total) AS sum_operations_total,
operations.client_id AS operations_client_id FROM "operations" GROUP
BY op
erations.client_id ORDER BY operations.value_date DESC LIMIT 3
=> {2=>#<BigDecimal:33001c8,''0.3018238553
424658E4'',27(45)>,
3=>#<BigDecimal:32ffbc8,''0.3028211589
041096E4'',27(45)>,
4=>#<BigDecimal:32ff5f
8,''0.3038730608 219178E4'',27(45)>}
irb(main):020:0> cc.class
=> ActiveSupport::OrderedHash
*2nd way:*
irb(main):021:0> cc =
Client.limit(3).joins(:operations).group(:lastname).sum(''operations.total'')
(0.0ms) SELECT SUM(operations.total) AS sum_operations_total, lastname
AS lastname FROM "clients" INNER JOIN "operations" ON
"operations
"."client_id" = "clients"."id" GROUP BY
lastname ORDER BY clients.lastname
LIMIT 3
=> {"AUBERT"=>3563.5837808219176,
"BERGER"=>3743.6309917808217,
"BERNARD"=>6624.027139726028}
Any idea ? Thank you.
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-talk/-/CCW6pnBrmEYJ.
For more options, visit https://groups.google.com/groups/opt_out.
Finally, here is the solution I came to:
@operations = Client.joins(:operations).select(''clients.id,firstname,
lastname, sum(total) as total'').group(''clients.id,
firstname,lastname,total'').paginate(page: params[:page])
It is not a will_paginate or smth eles problem, just PostgreSQL is more strict
with SQL standards, - it seems like you should pass all the selected
columns (colums defined in ''select'' clause) in the group by
clause, otherwise it will not work.
Regards
On Wednesday, October 24, 2012 2:53:22 PM UTC+2, Javix
wrote:>
> I have 2 models:
>
> class Operation < ActiveRecord::Base
> belongs_to :client
> *default_scope* order: ''operations.value_date DESC''
> end
>
> class Client < ActiveRecord::Base
> has_many :operations, dependent: :destroy
> * default_scope* order: ''clients.lastname''
> end
>
> Is there a more elegant way to get all the operations grouped by client
> with total sum calculated what is done as follows:
>
> @operations =
>
Operation.unscoped.includes(:client).order("clients.lastname").select("client_id,
> sum(total) as total").group("client_id").paginate(page:
params[:page])
>
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-talk/-/fBtNMGwUvzYJ.
For more options, visit https://groups.google.com/groups/opt_out.
Some correction has been applied:
@operations = Client.joins(:operations).select(''firstname, lastname,
sum(total) as total'').group(''clients.id,
firstname,lastname'').paginate(page: params[:page])
The same but in SQL:
select clients.firstname, clients.lastname, sum(operations.total) as total
from "clients"
INNER JOIN "operations" ON
"operations"."client_id" =
"clients"."id" GROUP
BY clients.id, clients.firstname,
clients.lastname order by clients.lastname
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-talk/-/1c11ACVX4BsJ.
For more options, visit https://groups.google.com/groups/opt_out.