Rolling your own pagination with will_paginate
So I’ve been working on some decent paging of time entries for Tempo’s reporting interface. I would have loved to have just ran with the fantastic will_paginate plugin, but we don’t generate what you see on the reporting screen in Tempo via a simple Rails find call on the Entry model. Instead we have a class that builds the appropriate SQL according to the various report specs and then runs it via find_by_sql. Actually, it does quite a bit more than that, but I don’t want to get off topic.
Anyway, this meant we couldn’t simply expect will_paginate to know what kind of SQL it should generate, because the standard options on find wouldn’t be enough. But the plugin was really quite well-designed for extensibility, and that made it a snap to get our own paging going on:
class ReportContext
...
# the list of entries to display based on context
def entries
self.prepare # make sure the SQL for this report is already built and sanitized
@offset = DEFAULT_OFFSET if @offset.blank? or @offset < 1
@limit = DEFAULT_LIMIT if @limit.blank? or @limit < 1
@entries = WillPaginate::Collection.create(@offset, @limit) do |pager|
# must use a duplicate or original sql is modified (this can get pretty vexing)
sql = Entry.send(:add_limit!, @sql.dup, :limit => pager.per_page, :offset => pager.offset)
result = Entry.find_by_sql([sql])
pager.replace(result)
unless pager.total_entries
# the pager didn't manage to guess the total count, do it manually
pager.total_entries = self.count # run the count sql for this report
end
end
end
Now that our @context.entries method is returning a will_paginate collection, we can pass it to the view helpers, and voila:
- paginated_section @context.entries, :params => { :action => :index } do
#data
%table#entry-list.tabular-list
%tbody
%tr#entry-header.header
%th{:style => "text-align: center;"}= sortable_column('Hours', 'hours')
%th= sortable_column('Date', 'occurred_on')
%th= sortable_column('Person', 'user_name')
%th= sortable_column('Project', 'project_name')
%th Tags
%th Description
%th= sortable_column('Created', 'created_at')
%th= sortable_column('Updated', 'updated_at')
%th.actions.noprint
- @context.entries.each_with_index do |entry, i|
= render :partial => 'entries/entry_tr', :locals => {:entry => entry}
Note how we’re passing the :action parameter into the :params option of the helper. This gets passed to the link generators that create the actual pagination links. Because this view is generated in a partial, any number of methods could have displayed it from differing XHR calls to different REST urls (entries/filter, entries/data, etc), and we have to make sure that any pagination links would bounce off the index action. By default the pagination will link to the URL of the current request.
Oh, right, so that sortable_column business? We’re finally adding this oft-requested feature into an upcoming update to Tempo.
Saturday, September 27, 2008
John Small says:
I had a similar problem, I have to generate my own SQL rather than rely on model.paginate_find. So I hunted through the documentation and found paginate_by_sql which does the job.
Is there any reason why you don't use it?
Tuesday, September 30, 2008
Billy says:
@John, that was my model for doing it, I just wanted a bit more control over the process and to run our own count sql code, generated elsewhere in the model.