# combine 'group' and 'order' in joined tables

**URL:** <https://discuss.rubyonrails.org/t/combine-group-and-order-in-joined-tables/65533>\
**Category:** rubyonrails-talk\
**Created:** [October 24, 2012, 12:53pm UTC](https://discuss.rubyonrails.org/t/combine-group-and-order-in-joined-tables/65533 "2012-10-24T12:53:22Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![belgoros](https://avatars.discourse-cdn.com/v4/letter/b/a587f6/32.png) [@belgoros](https://discuss.rubyonrails.org/u/belgoros)\
**Post date:** [October 24, 2012, 12:53pm UTC](https://discuss.rubyonrails.org/t/combine-group-and-order-in-joined-tables/65533/1 "2012-10-24T12:53:22Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![belgoros](https://avatars.discourse-cdn.com/v4/letter/b/a587f6/32.png) [@belgoros](https://discuss.rubyonrails.org/u/belgoros)\
**Post date:** [October 26, 2012, 12:26pm UTC](https://discuss.rubyonrails.org/t/combine-group-and-order-in-joined-tables/65533/2 "2012-10-26T12:26:20Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![belgoros](https://avatars.discourse-cdn.com/v4/letter/b/a587f6/32.png) [@belgoros](https://discuss.rubyonrails.org/u/belgoros)\
**Post date:** [October 27, 2012, 1:51pm UTC](https://discuss.rubyonrails.org/t/combine-group-and-order-in-joined-tables/65533/3 "2012-10-27T13:51:30Z")

</div>

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