Using will_paginate with named_scoped

Question

I have been having problems when using pagination along with named scope. The second page or any consecutive page of pagination using named_scope still returns result from the first page. Using find(:all) returns the pagination result correctly. Has anyone been able to use both them both correctly or experienced the same problem?

class Study < ActiveRecord::Base
  named_scope :opened, :conditions => {:study_stat => [205, 11203]}
end

#find(:all) returns correct studies for both pages
Study.find(:all, :conditions => {:study_stat => [205, 11203]}).paginate(:per_page => 10,  :page => 1).each {|e| pp e.study_number  }
Study.find(:all, :conditions => {:study_stat => [205, 11203]}).paginate(:per_page => 10,  :page => 2).each {|e| pp e.study_number  }

#using named_scope returns correct result for the first page but the second page has the same studies as ones in the page one.
Study.opened.paginate(:per_page => 10,  :page => 1).each {|e| pp e.study_number  }
Study.opened.paginate(:per_page => 10,  :page => 2).each {|e| pp e.study_number  }

Answer 1

Try including the :all option in your paginate call to the named_scope. I remember having a similar issue a while ago, and that fixed it for me.

So, instead of

Study.opened.paginate(:per_page => 10, :page => 2)

try

Study.opened.paginate(:all, :per_page => 10, :page => 2)

Answer 2

I found out why. As mentioned in the rdoc , the :order parameter is necessary to make the pagination works properly. This is especially true for named_scope. (I use SQL Server 2005)

User.active.paginate(:all, :per_page => 5,  :page => 1, :order=>'id').each {|e| pp e.user_name  }

User.active.paginate(:all, :per_page => 5,  :page => 2, :order=>'id').each {|e| pp e.user_name  }