#219 ActiveRecord::StatementInvalid when using custom select and having - will_paginate library

Type	To find
responsible:me	tickets assigned to you
tagged:"@high"	tickets tagged @high
milestone:next	tickets in the upcoming milestone
state:invalid	tickets with the state invalid
created:"last week"	tickets created last week
sort:number, importance, updated	tickets sorted by #, importance or updated
Combine keywords for powerful searching.
Use advanced searching »

#219 ✓invalid

ActiveRecord::StatementInvalid when using custom select and having

Reported by asherwood | May 14th, 2008 @ 11:32 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 @ 12:16 PM
- 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.
Mislav October 8th, 2008 @ 11:13 PM
- State changed from “open” to “invalid”
- Tag set to “will_paginate”
You flagged this item as spam.
Greg March 13th, 2009 @ 11:17 PM
untested hack that can be added to wp_count in will_paginate/lib/will_paginate/finder.rb to generate proper SQL.
```
        if count_options[:group] and count_options[:group] =~ /(.*)having/i
          count_options[:group] = $1
        end
```

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.