select with having doesn't work with 3.1.0

[slemrmartin]

Hi,
I'm upgrading from Ruby 1.9.3 Rails 3.2.22 will_paginate 3.0.3 to Ruby 2.1.8 Rails 4.2.6 will_paginate 3.1.0.

There is problem that WP skips selects when counting

• /lib/will_paginate/active_record.rb:select_for_count().

Queries doesn't work with HAVING clausule using something from select, as example below:

"SELECT user_perms.id AS up_id, group_perms.id AS gp_id, intranet_meetings.id AS meeting_id, user_perms.show_in_list AS up_show_in_list, group_perms.show_in_list AS gp_show_in_list, user_perms.show_detail AS up_show_detail, .... FROMintranet_meetings... HAVING ((up_id IS NOT NULL and 1 = 1 AND up_show_in_list = 1) OR (up_id IS NULL AND gp_id IS NOT NULL and 1 = 1 AND gp_show_in_list = 1))"

It seems that this method is new in version 3.1.0 and it seems like a bug..

[slemrmartin]

My solution:
I added total entries to paginate() method and monkey patch this method:

module WillPaginate
module ActiveRecord
module RelationMethods

  def count(*args)
    if limit_value
      return total_entries unless total_entries.blank?
      excluded = [:order, :limit, :offset, :reorder]
      excluded << :includes unless eager_loading?
      rel = self.except(*excluded)
      column_name = (select_for_count(rel) || :all)
      rel.count(column_name)
    else
      super(*args)
    end
  end
end

end
end

[henvo]

I'm encountering the same problem using Rails 4.2.5 and will_paginate 3.1.0.

My query essentially looks like this:

SELECT  *, (
            6365 * acos (
            cos ( radians( 15.12312 ) )
            * cos( radians( lat ) )
            * cos( radians( lon ) - radians( 20.12312) )
            + sin ( radians( 15.12312 ) )
            * sin( radians( lat ) )
            )
          ) AS distance FROM `location` HAVING distance < 10 

Will paginate creates the query:
SELECT COUNT(*) FROM 'location' HAVING distance < 10

This results in the mysql error:
Mysql2::Error: Unknown column 'distance' in 'having clause'

I will have a look at @slemrmartin fix - but still this bug makes will_paginate useless for any query with user defined variables in the SELECT part.

[slemrmartin]

2henvo>
It's not perfect solution, but is working for you too.

call count() separately before (without paginate) and then call paginate.
there is excerpt from my code:

arel = SomeModel.where('')
mysql_count = arel.klass.find_by_sql("SELECT COUNT(*) AS count_all FROM (" + arel.selection(...).to_sql + ") count")

arel.paginate(:page => params[:page], :per_page => 20, :total_entries => mysql_count.first[:count_all])

[mislav]

@slemrmartin That's indeed the reason why :total_entries was added as parameter. Will_paginate can't always successfully construct the correct COUNT query. Sometimes it needs to be manually constructed.

[mosh-tudor]

2henvo>
It's not perfect solution, but is working for you too.

call count() separately before (without paginate) and then call paginate.
there is excerpt from my code:

arel = SomeModel.where('')
mysql_count = arel.klass.find_by_sql("SELECT COUNT(*) AS count_all FROM (" + arel.selection(...).to_sql + ") count")

arel.paginate(:page => params[:page], :per_page => 20, :total_entries => mysql_count.first[:count_all])

You saved my day! Thanks!