Topic: pagination returning incorrect number of pages.

i have a controller where i need to return paginated records from two different Models.

the first one is Subscriptions, the other Requests. I am listing both on the same page, with ajax pagination.

the subscription pagination works fine:
[code = ruby]
@subscription_pages = Paginator.new(Subscription, Subscription.count, 10, params[:page])
    @subscriptions = Subscription.find(:all, :conditions => ["subscriptions.user_id = ?", session[:rbac_user].id],
        :include => [:request],
        :order => "requests.last_action DESC",
        :limit => @subscription_pages.items_per_page,
        :offset => @subscription_pages.current.offset)
[/code]

but on the requests, i couldnt use 'Request.count' as an argument for the Paginator constructor. when i did, pagination was showing 5+ pages even though i had only retrieved 3 records.  So i had to retrieve my records, use '@request.length' as the constructor argument, and retrieve the records again, with pagination options, like so:
[code = ruby]
@requests = Request.find(:all, :conditions => ["user_id = ?", session[:rbac_user].id])
@request_pages = Paginator.new(Request, @requests.length, 10, params[:page])
@requests = Request.find(:all, :conditions => ["user_id = ?", session[:rbac_user].id],
      :order => "last_action ASC",
        :limit => @request_pages.items_per_page,
        :offset => @request_pages.current.offset)
[/code]

and i'm pretty sure requesting the same records twice within a single method kills kittens and blinds small children.

Last edited by smoothoperatah (2007-01-10 12:42:22)

Re: pagination returning incorrect number of pages.

Are you using MySQL? You can pass SQL_CALC_FOUND_ROWS into the find query (using the :select parameter I think). Then grab the count using Request.count_by_sql('SELECT FOUND_ROWS()').

Railscasts - Free Ruby on Rails Screencasts

Re: pagination returning incorrect number of pages.

yeah mysql, but... wtf...

you lost me man..

Re: pagination returning incorrect number of pages.

SQL_CALC_FOUND_ROWS is a way to tell MySQL to count the total number of rows while fetching a limited amount. There's documentation on this page (look for FOUND_ROWS() function). Here's some code.

@requests = Request.find(:all, :conditions => ["user_id = ?", session[:rbac_user].id],
    :select => "SQL_CALC_FOUND_ROWS *",
    :order => "last_action ASC",
    :limit => @request_pages.items_per_page,
    :offset => @request_pages.current.offset)
@request_pages = Paginator.new(Request, Request.count_by_sql("SELECT FOUND_ROWS()"), 10, params[:page])

Untested.

Railscasts - Free Ruby on Rails Screencasts

Re: pagination returning incorrect number of pages.

yeah but wouldn't that throw an error because you are using the @request_pages variable before its instantiated.

Re: pagination returning incorrect number of pages.

Oops, yeah, you'll need to manually set those:

@requests = Request.find(:all, :conditions => ["user_id = ?", session[:rbac_user].id],
    :select => "SQL_CALC_FOUND_ROWS *",
    :order => "last_action ASC",
    :limit => 10,
    :offset => params[:page].to_i*10) # might need to adjust this

Railscasts - Free Ruby on Rails Screencasts

Re: pagination returning incorrect number of pages.

alright, ill give it a shot here in a min.