Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

undefined method `paginate' for #<Array:0x2d39d80> #273

Closed
Javix opened this Issue Oct 26, 2012 · 11 comments

Comments

Projects
None yet
2 participants

Javix commented Oct 26, 2012

I have my 2 modeks 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

The below query works fine in the console:

@operations = Client.all(:joins => :operations, :select => "clients.*, sum(operations.total) as total", :group => "clients.lastname", :limit => 3)

The result:

Loading development environment (Rails 3.2.8)
<ns, :select => "clients.*, sum(operations.total) as total", :group => "clients.lastname", :limit => 3)
  Client Load (15.6ms)  SELECT clients.*, sum(operations.total) as total FROM "clients" INNER JOIN "operations" ON "operations"."client_id"
= "clients"."id" GROUP BY clients.lastname ORDER BY clients.lastname LIMIT 3
  EXPLAIN (0.0ms)  EXPLAIN QUERY PLAN SELECT clients.*, sum(operations.total) as total FROM "clients" INNER JOIN "operations" ON "operations
"."client_id" = "clients"."id" GROUP BY clients.lastname ORDER BY clients.lastname LIMIT 3
EXPLAIN for: SELECT  clients.*, sum(operations.total) as total FROM "clients" INNER JOIN "operations" ON "operations"."client_id" = "clients
"."id" GROUP BY clients.lastname ORDER BY clients.lastname LIMIT 3
0|0|1|SCAN TABLE operations (~1000000 rows)
0|1|0|SEARCH TABLE clients USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|0|0|USE TEMP B-TREE FOR GROUP BY

=> [#<Client id: 32, firstname: "Laura", lastname: "AUBERT", phone: "0423871160", street: "Allée dumont", house: "284", box: nil, zipcode: "
18989", city: "Créteil", country: "FRANCE", created_at: "2012-10-24 08:47:27", updated_at: "2012-10-24 08:47:27">, #<Client id: 38, firstnam
e: "Victor", lastname: "BERGER", phone: "0673384052", street: "Impasse joly", house: "57", box: nil, zipcode: "65800", city: "Troyes", count
ry: "FRANCE", created_at: "2012-10-24 08:47:28", updated_at: "2012-10-24 08:47:28">, #<Client id: 27, firstname: "Pierre", lastname: "BERNAR
D", phone: "+33 240606392", street: "Rue huet", house: "6687", box: nil, zipcode: "88380", city: "Saint-denis", country: "FRANCE", created_a
t: "2012-10-24 08:47:26", updated_at: "2012-10-24 08:47:26">]

When I try to display the corresponding page with the results, I get:

`undefined method `paginate' for #<Array:0x2d39d80>

Any idea? I'm on Ruby 1.9.3, Rails 3.2.8. Thank you

Javix commented Oct 26, 2012

Thanks for the reply, but no, it didn't work:

 ArgumentError in OperationsController#index

:page parameter required

After adding page parameter as follows, it does not work either:

PG::Error: ERROR:  column "clients.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT  clients.*, sum(operations.total) as total FROM "clie...
                ^
: SELECT  clients.*, sum(operations.total) as total FROM "clients" INNER JOIN "operations" ON "operations"."client_id" = "clients"."id" GROUP BY clients.lastname ORDER BY clients.lastname LIMIT 30 OFFSET 0

So, try this:
Client.where(:joins => :operations, :select => "clients.*, sum(operations.total) as total", :group => "clients.lastname").paginate(:page => params[:page])

To limit registry per page, insert in to model: self.per_page = 3
I use globally WillPaginate.per_page = 10

Javix commented Oct 26, 2012

This time it's the syntax that is wrong:

PG::Error: ERROR:  column clients.joins does not exist
LINE 1: SELECT COUNT(*) FROM "clients"  WHERE "clients"."joins" = 'o...
                                              ^
: SELECT COUNT(*) FROM "clients"  WHERE "clients"."joins" = 'operations' AND "clients"."select" = 'clients.*, sum(operations.total) as total' AND "clients"."group" = 'clients.lastname'

P.S. I put the lmit of 3 just to have less records to analize.

Client.joins(:operations).select("clients.*, sum(operations.total) as total").group(:lastname).paginate(:page => params[:page])

Javix commented Oct 26, 2012

Not yet:

PG::Error: ERROR:  column "clients.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT  clients.*, sum(operations.total) as total FROM "clie...
                ^
: SELECT  clients.*, sum(operations.total) as total FROM "clients" INNER JOIN "operations" ON "operations"."client_id" = "clients"."id" GROUP BY lastname ORDER BY clients.lastname LIMIT 30 OFFSET 0

try Client.joins(:operations).select("clients.*, sum(operations.total) as total").group(:lastname).paginate(:page => params[:page]).uniq

Javix commented Oct 26, 2012

Noup, I don't think it is the right way we are doing:

PG::Error: ERROR:  column "clients.firstname" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT  clients.*, sum(operations.total) as total FROM "clie...
                ^
: SELECT  clients.*, sum(operations.total) as total FROM "clients" INNER JOIN "operations" ON "operations"."client_id" = "clients"."id" GROUP BY lastname, clients.id ORDER BY clients.lastname LIMIT 30 OFFSET 0

It will always ask all the fields because we put clients.* in the clause. More of that it will try to render clients.index page instead of operations.index one :(

Javix commented Oct 26, 2012

The version (I put again the limit of 3 to pull just # clients:

ruby-1.9.3-p0 :002 > operations = Operation.unscoped.limit(3).includes(:client).group(:client_id).sum(:total)
   (1.1ms)  SELECT SUM("operations"."total") AS sum_total, client_id AS client_id FROM "operations" GROUP BY client_id LIMIT 3
 => {43=>#<BigDecimal:a6e7f1c,'0.663981E4',18(18)>, 34=>#<BigDecimal:a6e7ddc,'0.566823E4',18(18)>, 1=>#<BigDecimal:a6e7c74,'0.306285E4',18(18)>} 
ruby-1.9.3-p0 :003 > 

works in the Rails console but when I try to call paginate I get again:

undefined method `paginate' for #<ActiveSupport::OrderedHash:0xa3fc62c>

the limit is unnecessary when using the will_paginate

you tried this query in the controller without paginate?

Javix commented Oct 26, 2012

Of course, I don't use 'limit' statement in the controller, - just in the console

Javix commented Oct 26, 2012

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

Regards

@Javix Javix closed this Oct 26, 2012

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment