ActiveRecord::StatementInvalid when using custom select and having
Reported by asherwood | May 14th, 2008 @ 02:06 AM
When using a custom select statement like the one provided in the following example
@results = Page.paginate(:all,
:select => "pages.*, ((ACOS(SIN(#{location.geom.y} * PI() / 180) * SIN(Y(GeomFromText( AsText(pages.geom))) * PI() / 180) + COS(#{location.geom.y} * PI() / 180) * COS(Y(GeomFromText( AsText(pages.geom))) * PI() / 180) * COS((#{location.geom.x} - X(GeomFromText( AsText(pages.geom)))) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance ",
:group => 'pages.id HAVING distance < 300',
:page => params[:page],
:per_page => 5)
An error is thrown due to will_paginate doing this
SELECT count(*) AS count_all, pages.id HAVING distance < 300 AS pages_id_having_distance_300
one way to solve this problem would be to allow for a custom count query to be provided.
Comments and changes to this ticket
-
Mislav May 16th, 2008 @ 03:16 AM
- → State changed from “new” to “open”
- → Assigned user changed from “Chris Wanstrath” to “Mislav”
Hey asherwood,
Indeed, you can provide your own count:
@results = Page.paginate :all, :select => "...", :group => 'pages.id HAVING distance < 300', :page => params[:page], :per_page => 5, :total_entries => Page.count(...)Observe the last parameter. When you provide :total_entries, will_paginate is going to use that and never perform a count by itself.
Please Login or create a free account to add a new comment.
You can update this ticket by sending an email to from your email client. (help)
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
Chris Wanstrath