Combine 'group' and 'order' in joined tables

Rails
1

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

2

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.

3

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

4

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