• From: Belgium
  • Registered: 2008-11-25
  • Posts: 1,355

Topic: Combine select, order and group clause with postgresql (pg gem)

I can't figure out the right syntax to use with order, group and select clause. The below one raises an error on Postgresql DB:

@operations = Operation.unscoped.includes(:client).order("clients.lastname").select("client_id, sum(total) as total").group("client_id")

The models are defined as follows:

class Client < ActiveRecord::Base
  has_many :operations, dependent: :destroy
  default_scope order: 'clients.lastname'
end

class Operation < ActiveRecord::Base
  belongs_to :client  
  default_scope order: 'operations.value_date DESC'
end

It works on sqlite3 but fails on postgresql.
Any idea? Thank you.

Last edited by Javix (2012-10-25 03:28:38)

  • From: Belgium
  • Registered: 2008-11-25
  • Posts: 1,355

Re: Combine select, order and group clause with postgresql (pg gem)

Even if the below solutions 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.

  • From: Belgium
  • Registered: 2008-11-25
  • Posts: 1,355

Re: Combine select, order and group clause with postgresql (pg gem)

Finally, here is the solution I came to:

@operations = Client.joins(:operations).select('firstname, lastname, sum(total) as total').group('clients.id, firstname,lastname').paginate(page: params[:page])

The same 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

It is not a will_paginate or smth else problem, just PostgreSQL is more strict with SQL standards, - it seems like you should pass all the selected
columns (columns defined in 'select' clause) in the group by clause, otherwise it will not work.

Regards

Last edited by Javix (2012-10-27 09:47:23)