Paginating special queries

The will_paginate plugin for Rails is one of the best pagination plugins I know of and I like the ease of using it. Instead of using Model.find, simply use Model.paginate(:page => x) and you'll get back a collection that behaves like an array and can be displayed in views like any other collection. Additionally the returned collection has some new methods (like page_count and total_pages), which are useful to display pagination links. Installing the will_paginate plugin adds the pagination class method to all models; it even works with automatic finders: e.g. you can use Model.paginate_by_attr, which will paginate queries for Model.find_by_attr.

However, pagination can become tricky if you want to paginate collections returned by special queries...

Pagination works fine when using the find method or an automatic finder method directly with a model or through an association. Consider the following models:

class Group < ActiveRecord::Base
  has_many :users
end
class User < ActiveRecord::Base
  belongs_to :group
end

All of the following would work perfectly:

@active_users = User.paginate_by_status('active', :page => x)
@active_users = @group.users.paginate_by_status('active', :page => x)
@active_moderators = @group.users.paginate(:conditions => ['status = ? AND role = ?', 'active', 'moderator'], :page => x)

However, you'll probably want to avoid doing queries with that many SQL conditions in a controller. Special queries like the above should stay out of a controller and better be moved into the model.

Method 1: Additional has_many associations

Easiest way and what first comes into mind is to add an extra has_many association to the model. Like described in The Rails Way: Association Proxies (Case Three):

class Group < ActiveRecord::Base
  has_many :users
  has_many :active_moderators, :class_name => 'User', :conditions => ['status = ? AND role = ?', 'active', 'moderator']
end

Now you can easily paginate a list of active moderators of a group:

@active_moderators = @group.active_moderators.paginate(:page => x)

This also works if you pass additional options, e.g. to get a sorted list:

@active_moderators = @group.active_moderators.paginate(:order => 'username', :page => x)

The above method works well, but has some drawbacks: 1. You can only use the special query through the association. You cannot e.g. get a global list of all active moderators. 1. The code (the SQL conditions) which determines the filter to apply on the User collection is now kept in the Group model whereas it would better fit into the User model itself. 1. If you have multiple models that have hasmany associations with the User model, you need to duplicate the extra hasmany associations into every other model. That's error-prone and not DRY.

Method 2: Custom finder methods

Another method to refactor a special query to the model, would be a custom class method (a custom finder) for the model:

class User < ActiveRecord::Base
  def self.find_active_moderators (options = {})
    with_scope(:find => { :conditions => ['status = ? AND role = ?', 'active', 'moderator']})
      find(:all, options)
    end
  end
end

Using with_scope ensures, that the special conditions are applied while still allowing us to pass additional options like :order (or :limit and :offset, which are used by the pagination plugin). Now, all code to query a special user collection stays within the User model and can be used globally or through associated models:

@active_moderators = User.find_active_moderators(:order => 'username')
@active_group_moderators = @group.users.find_active_moderators(:order => 'username')

The willpaginate plugin is smart enough to also use custom finder methods. Using User.paginateactivemoderators will internally use User.findactive_moderators to fetch the collection (where :limit and :offset options are applied to retrieve the collection for the given page):

@active_moderators = @group.users.paginate_active_moderators(:order => 'username', :page => x)

Cool, isn't it? But wait! There's still one problem: pagination relies on getting a correct count of the total entries to display. This is done by querying the database for the number of records. With a custom finder method, the willpaginate plugin isn't able to get the correct number of records to display, because the custom finder method encapsulates the conditions. Willpaginate therefore takes the number of total records instead, which results in a view that shows the correct entries, but displays additional empty pages (Ticket #66).

The only way I found to work around this, was to pass the correct number of records to the pagination plugin. Therefore, for every custom finder method, I also created a custom counter method:

class User < ActiveRecord::Base
  def self.count_active_moderators (options = {})
    with_scope(:find => { :conditions => ['status = ? AND role = ?', 'active', 'moderator']})
      count(:all, options)
    end
  end
end

Unfortunately, this results in code duplication again (custom finder and count methods do use the same withscope statement, but call different methods within the block. This could be refactored to a separate method, e.g. withactivemoderators). Furthermore, willpaginate does not automatically use a custom counter method, so you have to add the :total_entries option to paginate calls in the controller:

@active_moderators = @group.users.paginate_active_moderators(:order => 'username', :page => x, :total_entries => @group.users.count_active_moderators)

Hopefully some day, the will_paginate plugin will come up with an easier solution. Maybe by automatically using a custom count method, or -- even better -- by somehow automatically counting the records within the right scope.

Another method could be to use the scope_out plugin, which automatically creates model class methods for a given scope. However, I didn't look into this plugin yet and don't know if it works well with will_paginate.

Update (2007-08-02): The willpaginate plugin has changed a bit when it comes to custom finder methods. It now calls findwhatever when using paginatewhatever (formerly it used to call findall_whatever). I changed the above text accordingly.

Update (2007-08-03): I checked out the scope_out plugin today, and liked it a lot. However, using willpaginate with scopeout suffers from the same problem I described above, since behind the scenes, scope_out creates custom finder methods like the ones I described above (although it simplifies it by providing a single method call to create finder, scope and calculation methods).

Update (2007-10-20): The best solution I found is to use the HasFinder plugin, which combines the features of scopeout and scopedproxy and works well with will_paginate.

• Tweet