[cgiapp] Q: Any guidelines for paging thru db records via CGI params?

[Cees Hek]

On Mon, May 26, 2008 at 7:53 PM, Michael Lackhoff
<lackhoff at zbmed.uni-koeln.de> wrote:
> I am very interested, especially since I am using the same tools
> (CAP+TT+Rose). So, what would be even better than the plugin source is
> an example how you get everything organized. Just some "copy and paste"
> or whatever is quick and easy for you.

OK,  briefly, here is how I use my pager plugin.  First I have a
helper method in my CGIApp base class to simplify paging and
searching:

sub paged_results {
    my $self = shift;
    my $object_class = shift;
    my $search = shift || {};
    my $pager_defaults = shift || {};

    my $total = Rose::DB::Object::Manager->get_objects_count(
        %$search,
        object_class => $object_class,
    );
    my $pager = $self->pager( { %$pager_defaults, entries => $total } );
    my $objects = Rose::DB::Object::Manager->get_objects(
        %$search,
        object_class => $object_class,
        page => $pager->page,
        per_page => $pager->per_page,
    );

    return ($objects, $pager);
}

You pass it the table class you want to search, and a hashref of
options that the get_objects method accepts (this is usually just the
query => [] option, and possibly a sort_by).  Lastly, it will accept
some defaults for the pager object (like how many entries per page).
It will choose appropriate defaults if you exclude this (the only
required option is the 'entries' value which gives the total number of
entries in the search result).

You get back a list of Rose::DB::Object items, and a pager object.
These are then passed to the template (if you use Template::Toolkit
that is, since HTML::Template by itself can't do anything with
objects).


I have some MACROs that do common things in my templates, and one of
these is for paging:

[% MACRO render_pager( pager ) BLOCK %]
[%   IF pager.last_page > 1 %]
<div class="paging_controls">
<span class="pagelink"> page [% pager.page %] of [% pager.last_page %] </span>
[%     IF NOT pager.on_first_page %]
<span class="pagelink"><a href="[% pager.url( page=pager.previous_page
) %]" title="Previous page">&laquo;</a></span>&nbsp;
[%     END %]
[%     FOREACH page IN [1..pager.last_page] %]
[%       IF pager.is_current_page(page) %]
<span class="pagecurrent">[% page %]</span>&nbsp;
[%       ELSE %]
<span class="pagelink"><a href="[% pager.url( page=page ) %]"
title="[% page %]">[% page %]</a></span>&nbsp;
[%       END %]
[%     END %]
[%     IF NOT pager.on_last_page %]
<span class="pagelink"><a href="[% pager.url( page=pager.next_page )
%]" title="Next page">&raquo;</a></span>&nbsp;
[%     END %]
<form class="pagelink" action="[% pager.formurl %]" type="GET">[%
pager.hidden_params( skip=['page'] ) %]Jump to Page <input type="text"
name="page" size="2" /></form>
</div>
[%   END %]
[% END %]

This pager example is only good for a fixed number of pages and would
break down if you had hundreds or thousands of pages.  However, it
would be simple to create another macro that only showed the pager in
a different way.

So from the template above you can see that the pager object has at
least the following methods:

page
last_page
next_page
previous_page
on_first_page
on_last_page
url
formurl
hidden_params

Most of those are self explanatory.  The url method is (IMHO) the best
part, since you don't have to worry about building URLs yourself.  It
just takes the current URL (ie whatever loaded this page), and then
replaces any values that you pass in (usually that is only the 'page'
parameter).  Most of the methods will be similar to what the
Data::Page module provides (and it probably makes sense to syncronise
method names with Data::Page), but what Data::Page is missing is the
URL generation which IMO is usually the most annoying thing to deal
with.

So summing all of this up, my runmode and template will only need
something like the following:

my list_contacts {
  my $self = shift;

  %search_params;
  $search_params{sort_by} = $self->query->param('sort_by');
  $search_params{query} = [ name => { ilike =>
'%'.$self->query->param('name').'%' } ];

  my ($contacts, $pager) = $self->paged_results( 'My::DB::Contact',
\%search_params );

  return $self->tt_process( { contacts => $contacts, pager => $pager } );
}


[% PROCESS macro/pager.macro %]
[% render_pager( pager ) %]
   <table>
    <thead>
     <tr>
      <td>Name</td>
      <td>Email</td>
      <td></td>
     </tr>
    </thead>
    <tbody>
[% FOREACH contact IN contacts %]
     <tr>
      <td>[% contact.name %]</td>
      <td>[% contact.email %]</td>
     </tr>
[% END %]
    </tbody>
   </table>
[% render_pager( pager ) %]


Please note that this is not exactly what I run in my apps and some of
the above was simplified and edited for this email, so excuse any
typos please.

Also, for those of you that use HTML::Template, it would be easy to
use the $pager object to build a method in your base class that builds
the same thing that my TT MACRO builds and return it as a string that
can be passed to the template.  Just passing the values to the
template may not be enough, because you would loose the ability to use
the 'url' method, and in that case you might as well just use
Data::Page.

Comments are welcome, and if there is interest, I wouldn't mind some
help hashing things out and releasing this as a proper plugin.

Cheers,

Cees