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])
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}
@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
