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

[Ron Savage]

Hi Folks

Let's say I offered the user a form with 2 fields, id and (Winemaker's)
name.

The user enters B% for the name's value, and, using Rose, I know there
are 18 matching records.

But it happens I'm using a per_page of 10 (say) to call
get_winemaker_iterator(), so the first call returns records 1 .. 10 to
the user.

What's a good way to display buttons so the user can page forward to get
records 11 .. 18, and (later) back from page 2 to page 1?

Alternatives:

o I could pre-populate the form, and offer Next and Previous buttons,
besides the Submit button. Then I'd have to also offer a Clear button
for when the user wishes to start a new search

o I could not use that form, but create a separate one, with previous
input used to populate hidden fields, so the only fields visible are
Previous and Next

o I could use a cookie to send the previous input around, and again
offer just Previous and Next (separate from the original form)

o I could use non-cookie sessions, but otherwise work as per the
previous item

Comments?

--
Ron Savage
ron@...
http://savage.net.au/index.html



#####  CGI::Application community mailing list  ################
##                                                            ##
##  To unsubscribe, or change your message delivery options,  ##
##  visit:  http://www.erlbaum.net/mailman/listinfo/cgiapp    ##
##                                                            ##
##  Web archive:   http://www.erlbaum.net/pipermail/cgiapp/   ##
##  Wiki:          http://cgiapp.erlbaum.net/                 ##
##                                                            ##
################################################################

[Dan Horne]

Ron Savage said:

> Hi Folks
>
> Let's say I offered the user a form with 2 fields, id and (Winemaker's)
> name.
>
> The user enters B% for the name's value, and, using Rose, I know there
> are 18 matching records.
>
> But it happens I'm using a per_page of 10 (say) to call
> get_winemaker_iterator(), so the first call returns records 1 .. 10 to
> the user.
>
> What's a good way to display buttons so the user can page forward to get
> records 11 .. 18, and (later) back from page 2 to page 1?

I like Data::Page::Navigation. Each link for selecting a page would
include the search criteria including the page number. DBIx::Class offers
a database agnostic syntax for limiting the rows returned to the page
requested, so I guess Rose would too.


#####  CGI::Application community mailing list  ################
##                                                            ##
##  To unsubscribe, or change your message delivery options,  ##
##  visit:  http://www.erlbaum.net/mailman/listinfo/cgiapp    ##
##                                                            ##
##  Web archive:   http://www.erlbaum.net/pipermail/cgiapp/   ##
##  Wiki:          http://cgiapp.erlbaum.net/                 ##
##                                                            ##
################################################################

[Ron Savage]

Hi Dan

> I like Data::Page::Navigation. Each link for selecting a page would
> include the search criteria including the page number. DBIx::Class offers

I'm intending to use Data::Page.

> a database agnostic syntax for limiting the rows returned to the page
> requested, so I guess Rose would too.

Yes it does. The question is how to let the end user manage the
transition from page to page.

--
Ron Savage
ron@...
http://savage.net.au/index.html



#####  CGI::Application community mailing list  ################
##                                                            ##
##  To unsubscribe, or change your message delivery options,  ##
##  visit:  http://www.erlbaum.net/mailman/listinfo/cgiapp    ##
##                                                            ##
##  Web archive:   http://www.erlbaum.net/pipermail/cgiapp/   ##
##  Wiki:          http://cgiapp.erlbaum.net/                 ##
##                                                            ##
################################################################

[Dan Horne]

Ron Savage said:
> The question is how to let the end user manage the
> transition from page to page.

Oh, I misunderstood. I guess in that case, why not use the pagination
model used by Google with the page number at the footer of the page



#####  CGI::Application community mailing list  ################
##                                                            ##
##  To unsubscribe, or change your message delivery options,  ##
##  visit:  http://www.erlbaum.net/mailman/listinfo/cgiapp    ##
##                                                            ##
##  Web archive:   http://www.erlbaum.net/pipermail/cgiapp/   ##
##  Wiki:          http://cgiapp.erlbaum.net/                 ##
##                                                            ##
################################################################

[Cees Hek]

On Mon, May 26, 2008 at 1:36 PM, Ron Savage <ron@...> wrote:
> Hi Dan
>
>> I like Data::Page::Navigation. Each link for selecting a page would
>> include the search criteria including the page number. DBIx::Class offers
>
> I'm intending to use Data::Page.

Hi Ron,

If you are using Rose, then you don't need to bother with Data::Page.
Rose provides convenience methods to do your limit/offset values.

$products = Product::Manager->get_products(
      query =>
      [
         ...
      ],
      per_page   => 10,
      page  => 2,
    );

Then in your prev/next links just include the page number they are on,
and optionally the number of items per page.

>> a database agnostic syntax for limiting the rows returned to the page
>> requested, so I guess Rose would too.
>
> Yes it does. The question is how to let the end user manage the
> transition from page to page.

If it is a search result that you are displaying, then I usually just
pass the original search parameters along with the page number in the
prev and next links.  The benefit of that is that you don't need to
worry about sessions, or storing the search results.  The potential
drawback is that the search results could change from page to page if
the data being searched changes rapidly (ie a new product may be added
that matches the search criteria which will bump the search results
around a bit).  I don't worry about that too much though.


I wrote a CGI::App plugin that does paging a few years ago, but I
never released it.  It manages all the URL generation for you, and all
you have to do is give it the total number of search results.  It
gleans the other info (like page number) from the current query
params, so it can properly highlight what page you are on.  I use it
with Rose::DB::Object and it makes life very easy.

One drawback you might have on my solution is that it uses
Template::Toolkit, and I believe you are still in the HTML::Template
camp, so it may not be right for you.  It could be made to work with
HTML::Template though.

I am willing to share if anyone is interested.

Cheers,

Cees

#####  CGI::Application community mailing list  ################
##                                                            ##
##  To unsubscribe, or change your message delivery options,  ##
##  visit:  http://www.erlbaum.net/mailman/listinfo/cgiapp    ##
##                                                            ##
##  Web archive:   http://www.erlbaum.net/pipermail/cgiapp/   ##
##  Wiki:          http://cgiapp.erlbaum.net/                 ##
##                                                            ##
################################################################

[Ron Savage]

On Mon, 2008-05-26 at 16:15 +1200, Dan Horne wrote:
> Ron Savage said:
> > The question is how to let the end user manage the
> > transition from page to page.
>
> Oh, I misunderstood. I guess in that case, why not use the pagination
> model used by Google with the page number at the footer of the page

Brilliant! Search for search in order to find out how to search :-)!
--
Ron Savage
ron@...
http://savage.net.au/index.html



#####  CGI::Application community mailing list  ################
##                                                            ##
##  To unsubscribe, or change your message delivery options,  ##
##  visit:  http://www.erlbaum.net/mailman/listinfo/cgiapp    ##
##                                                            ##
##  Web archive:   http://www.erlbaum.net/pipermail/cgiapp/   ##
##  Wiki:          http://cgiapp.erlbaum.net/                 ##
##                                                            ##
################################################################

[Ron Savage]

Hi Cees

> If you are using Rose, then you don't need to bother with Data::Page.
> Rose provides convenience methods to do your limit/offset values.

Yes, I was wondering if I needed it at all.

> $products = Product::Manager->get_products(
>       query =>
>       [
>          ...
>       ],
>       per_page   => 10,
>       page  => 2,
>     );

Yep, already doing that.

> Then in your prev/next links just include the page number they are on,
> and optionally the number of items per page.

I decided to pre-populate the search fields with the user's previous
input, so now all I have to do is set the page number.

It's the precise mechanism for this I'm undecided about.

I'm just testing the onclick way to fiddle the page number with JS
(previous => -1, next => +1) and submit the form.

(Runs test. Ahhhgggg. The JS function is not being called)

> >> a database agnostic syntax for limiting the rows returned to the page
> >> requested, so I guess Rose would too.
> >
> > Yes it does. The question is how to let the end user manage the
> > transition from page to page.
>
> If it is a search result that you are displaying, then I usually just
> pass the original search parameters along with the page number in the
> prev and next links.  The benefit of that is that you don't need to
> worry about sessions, or storing the search results.  The potential

Yes, I'm going to avoid sessions (including cookies) for as long as
possible.

> drawback is that the search results could change from page to page if
> the data being searched changes rapidly (ie a new product may be added
> that matches the search criteria which will bump the search results
> around a bit).  I don't worry about that too much though.

I won't either.

> I wrote a CGI::App plugin that does paging a few years ago, but I
> never released it.  It manages all the URL generation for you, and all
> you have to do is give it the total number of search results.  It
> gleans the other info (like page number) from the current query
> params, so it can properly highlight what page you are on.  I use it
> with Rose::DB::Object and it makes life very easy.

Hmmmm.

> One drawback you might have on my solution is that it uses
> Template::Toolkit, and I believe you are still in the HTML::Template
> camp, so it may not be right for you.  It could be made to work with
> HTML::Template though.

Yes, I always default to HTML::Template.

> I am willing to share if anyone is interested.

I'm using CGI::Application::Dispatch. Does that make your module redundant?

If not, and since I'm working at home most of the time at the moment,
perhaps we could turn it into a CPAN-ready module. Let's see if there is
any interest.

--
Ron Savage
ron@...
http://savage.net.au/index.html



#####  CGI::Application community mailing list  ################
##                                                            ##
##  To unsubscribe, or change your message delivery options,  ##
##  visit:  http://www.erlbaum.net/mailman/listinfo/cgiapp    ##
##                                                            ##
##  Web archive:   http://www.erlbaum.net/pipermail/cgiapp/   ##
##  Wiki:          http://cgiapp.erlbaum.net/                 ##
##                                                            ##
################################################################

[Ron Savage]

Hi Cees

> > If you are using Rose, then you don't need to bother with Data::Page.
> > Rose provides convenience methods to do your limit/offset values.
>
> Yes, I was wondering if I needed it at all.

Actually, there is a use for Data::Page. It can tell you that you're on
the last page, which means you don't output the Next button. Same for
first page of course.

--
Ron Savage
ron@...
http://savage.net.au/index.html



#####  CGI::Application community mailing list  ################
##                                                            ##
##  To unsubscribe, or change your message delivery options,  ##
##  visit:  http://www.erlbaum.net/mailman/listinfo/cgiapp    ##
##                                                            ##
##  Web archive:   http://www.erlbaum.net/pipermail/cgiapp/   ##
##  Wiki:          http://cgiapp.erlbaum.net/                 ##
##                                                            ##
################################################################

[Michael Lackhoff]

Hi Cees,

> I wrote a CGI::App plugin that does paging a few years ago, but I
> never released it.  It manages all the URL generation for you, and all
> you have to do is give it the total number of search results.  It
> gleans the other info (like page number) from the current query
> params, so it can properly highlight what page you are on.  I use it
> with Rose::DB::Object and it makes life very easy.
>
> One drawback you might have on my solution is that it uses
> Template::Toolkit, and I believe you are still in the HTML::Template
> camp, so it may not be right for you.  It could be made to work with
> HTML::Template though.
>
> I am willing to share if anyone is interested.

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.

Thanks
-Michael

--
Michael Lackhoff
ZB MED Medizin. Gesundheit. / EDV-Abteilung
Fon: + 49 (0) 221 478-7105
Michael.Lackhoff@...


#####  CGI::Application community mailing list  ################
##                                                            ##
##  To unsubscribe, or change your message delivery options,  ##
##  visit:  http://www.erlbaum.net/mailman/listinfo/cgiapp    ##
##                                                            ##
##  Web archive:   http://www.erlbaum.net/pipermail/cgiapp/   ##
##  Wiki:          http://cgiapp.erlbaum.net/                 ##
##                                                            ##
################################################################

[Cees Hek]

On Mon, May 26, 2008 at 7:53 PM, Michael Lackhoff
<lackhoff@...> 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">«</a></span> 
[%     END %]
[%     FOREACH page IN [1..pager.last_page] %]
[%       IF pager.is_current_page(page) %]
<span class="pagecurrent">[% page %]</span> 
[%       ELSE %]
<span class="pagelink"><a href="[% pager.url( page=page ) %]"
title="[% page %]">[% page %]</a></span> 
[%       END %]
[%     END %]
[%     IF NOT pager.on_last_page %]
<span class="pagelink"><a href="[% pager.url( page=pager.next_page )
%]" title="Next page">»</a></span> 
[%     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

#####  CGI::Application community mailing list  ################
##                                                            ##
##  To unsubscribe, or change your message delivery options,  ##
##  visit:  http://www.erlbaum.net/mailman/listinfo/cgiapp    ##
##                                                            ##
##  Web archive:   http://www.erlbaum.net/pipermail/cgiapp/   ##
##  Wiki:          http://cgiapp.erlbaum.net/                 ##
##                                                            ##
################################################################

[Michael Lackhoff]

On 26 May 2008 at 23:13, Cees Hek wrote:

Many thanks Cees, I think that should get me going. The only missing
part is -- well, the plugin. Or did I miss something? At least I didn't
see an attachment or link.

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

what do you mean by "at least"? Because it is work in progress or is
there some modular system within your plugin?

> 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

I already have a url method in my base class. I use it to build a URL
that is both pretty and what CAP::Dispatch expects, e.g. I feed
(customers, edit, 342) to it and get
'http://myserver/customers/edit/342' back (looks trivial but also
handles lots of variants). I am not sure yet how to best avoid a naming
conflict but there will be ways.

-Michael
--
Michael Lackhoff
ZB MED Medizin. Gesundheit. / EDV-Abteilung
Fon: + 49 (0) 221 478-7105
Michael.Lackhoff@...


#####  CGI::Application community mailing list  ################
##                                                            ##
##  To unsubscribe, or change your message delivery options,  ##
##  visit:  http://www.erlbaum.net/mailman/listinfo/cgiapp    ##
##                                                            ##
##  Web archive:   http://www.erlbaum.net/pipermail/cgiapp/   ##
##  Wiki:          http://cgiapp.erlbaum.net/                 ##
##                                                            ##
################################################################

[Cees Hek]

On Mon, May 26, 2008 at 11:56 PM, Michael Lackhoff
<lackhoff@...> wrote:
> On 26 May 2008 at 23:13, Cees Hek wrote:
>
> Many thanks Cees, I think that should get me going. The only missing
> part is -- well, the plugin. Or did I miss something? At least I didn't
> see an attachment or link.

I need to clean up a few things before I can publish it publically.

>> So from the template above you can see that the pager object has at
>> least the following methods:
>
> what do you mean by "at least"? Because it is work in progress or is
> there some modular system within your plugin?

I didn't actually look at the plugin source when writing my examples
:)  There may be a couple other helper methods that were not used in
my example, but I don't remember off hand.

>> 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
>
> I already have a url method in my base class. I use it to build a URL
> that is both pretty and what CAP::Dispatch expects, e.g. I feed
> (customers, edit, 342) to it and get
> 'http://myserver/customers/edit/342' back (looks trivial but also
> handles lots of variants). I am not sure yet how to best avoid a naming
> conflict but there will be ways.

There will be no conflict.  The plugin only exports one method called
'pager'.  When you call the pager method, it returns an object, and
the object has all of the above methods.

I have a URL method in my base class that does similar things as well.
 But the pager plugin doesn't need to know about this, because it is
only making very simple changes to the existing URL that was called.
All it is doing is taking the current URL and changing the 'page'
parameter.  This would only be a problem if the page parameter is part
of the path_info, instead of being a query param.

Cheers,

Cees

#####  CGI::Application community mailing list  ################
##                                                            ##
##  To unsubscribe, or change your message delivery options,  ##
##  visit:  http://www.erlbaum.net/mailman/listinfo/cgiapp    ##
##                                                            ##
##  Web archive:   http://www.erlbaum.net/pipermail/cgiapp/   ##
##  Wiki:          http://cgiapp.erlbaum.net/                 ##
##                                                            ##
################################################################

[Stephen Carville-6]

On Sun, May 25, 2008 at 6:37 PM, Ron Savage <ron@...> wrote:

> Hi Folks
>
> Let's say I offered the user a form with 2 fields, id and (Winemaker's)
> name.
>
> The user enters B% for the name's value, and, using Rose, I know there
> are 18 matching records.
>
> But it happens I'm using a per_page of 10 (say) to call
> get_winemaker_iterator(), so the first call returns records 1 .. 10 to
> the user.
>
> What's a good way to display buttons so the user can page forward to get
> records 11 .. 18, and (later) back from page 2 to page 1?

Does your SQl support the LIMIT command?  MySQL and PostgreSQL both do
but with slightly different syntax.  I think Oracle does too but SQL
Server does not.

You could do somehing like (MySQL):

select count (*)
from   wherever
where whatever=whomever;

This returns a total number of rows: $lastrow.

Then make the first call

$startrow=0;
$display=10;

select stuff, more_stuff, last_stuff
from    wherever
where whatever=whomever
order by whatever
limit  $startrow, $display;

This get you the first ten rows so next page would be

$startrow+=$display;

select stuff, more_stuff, last_stuff
from    wherever
where whatever=whomever
order by whatever
limit  $startrow, $display;

 You have to track $lastrow anyway to know where to stop displaying
next in the form (well not really but the logic is tricky) so you
could create a drop down box to jump to any page.

If your DB has plenty of temp space then the extra time for the LIMIT
command is small.

--
Stephen Carville

#####  CGI::Application community mailing list  ################
##                                                            ##
##  To unsubscribe, or change your message delivery options,  ##
##  visit:  http://www.erlbaum.net/mailman/listinfo/cgiapp    ##
##                                                            ##
##  Web archive:   http://www.erlbaum.net/pipermail/cgiapp/   ##
##  Wiki:          http://cgiapp.erlbaum.net/                 ##
##                                                            ##
################################################################

[Ron Savage]

Hi Stephen

> Does your SQl support the LIMIT command?  MySQL and PostgreSQL both do
> but with slightly different syntax.  I think Oracle does too but SQL
> Server does not.

Understood, but Rose handles those sorts of things. And I use Postgres,
but other users of my code may not.

As I said in another reply, I'm fishing for ideas on how to display
these options in the web client, for the user's benefit.

--
Ron Savage
ron@...
http://savage.net.au/index.html



#####  CGI::Application community mailing list  ################
##                                                            ##
##  To unsubscribe, or change your message delivery options,  ##
##  visit:  http://www.erlbaum.net/mailman/listinfo/cgiapp    ##
##                                                            ##
##  Web archive:   http://www.erlbaum.net/pipermail/cgiapp/   ##
##  Wiki:          http://cgiapp.erlbaum.net/                 ##
##                                                            ##
################################################################

[Bradley C Bailey-2]

Ron,

> Understood, but Rose handles those sorts of things. And I use Postgres,
> but other users of my code may not.
>
> As I said in another reply, I'm fishing for ideas on how to display
> these options in the web client, for the user's benefit.

I usually just give the user a p