Combine 'group' and 'order' in joined tables

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}

Any idea ? Thank you.

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

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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs