Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

ActiveRecord::StatementInvalid when using custom select and having

#219

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.

Reported by asherwood · May 14th, 2008 @ 11:32 AM

State: invalid
Milestone: none
Assigned to: Mislav Mislav

Activity

  1. Mislav
    Mislav
    • 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.

    May 16th, 2008 @ 12:16 PM

  2. Mislav
    Mislav
    • State changed from open to invalid
    • Tag set to will_paginate

    October 8th, 2008 @ 11:13 PM

  3. Greg
    Greg

    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

    March 13th, 2009 @ 11:17 PM

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.