Class::DBI::Lite::Pager - Page through your records, easily.

  # Say we're on page 1 of a list of all 'Rock' artists:
  my $pager = app::artist->pager({
    genre => 'Rock',
  }, {
    order_by    => 'name ASC',
    page_number => 1,
    page_size   => 20,
  });

  # -------- OR -----------
  my $pager = app::artist->sql_pager({
    data_sql  => "SELECT * FROM artists WHERE genre = ?",
    count_sql => "SELECT COUNT(*) FROM artists WHERE genre = ?",
    sql_args  => [ 'Rock' ],
  }, {
    page_number => 1,
    page_size   => 20,
  });

  # Get the first page of items from the pager:
  my @artists = $pager->items;

  # Is the a 'previous' page?:
  if( $pager->has_prev ) {
    print "Prev page number is " . ( $pager->page_number - 1 ) . "\n";
  }

  # Say where we are in the total scheme of things:
  print "Page " . $pager->page_number . " of " . $pager->total_pages . "\n";
  print "Showing items " . $pager->start_item . " through " . $pager->stop_item . " out of " . $pager->total_items . "\n";

  # Is there a 'next' page?:
  if( $pager->has_next ) {
    print "Next page number is " . ( $pager->page_number + 1 ) . "\n";
  }

  # Get the 'start' and 'stop' page numbers for a navigation strip with 
  # up to 5 pages before and after the 'current' page:
  my ($start, $stop) = $pager->navigations( 5 );
  for( $start..$stop ) {
    print "Page $_ | ";
  }

  # Fetch 300,000,000 records, 100 records at a time:
  my $pager = app::citizen->pager({
    country => 'USA'
  }, {
    order_by    => 'last_name, first_name',
    page_size   => 100,
    page_number => 1,
  });
  while( my @people = $pager->next_page ) {
    # We only got 100 people, instead of swamping the 
    # database by asking for 300M records all at once:
  }

Paging through records should be easy. Class::DBI::Lite::Pager makes it easy.

This has been tested with MySQL 5.x and SQLite. It should work with any database that provides some kind of LIMIT index, offset construct.

To discover the total number of pages and items, 2 queries must be performed:

1 First we do a SELECT COUNT(*) ... to find out how many items there are in total.

2 One or more queries to get the records you've requested.

If running 2 queries is going to cause your database server to catch fire, please consider rolling your own pager or finding some other method of doing this.

Returns a new Pager object at the page number and page size specified.

Read only. Returns the page number.

Read only. Returns the page size.

Read only. Returns the total number of pages in the Pager.

Read only. Returns the total number of records in all the pages combined.

Read only. Returns the index of the first item in this page's records.

Read only. Returns the index of the last item in this page's records.

Read only. Returns true or false depending on whether there are more pages after the current page.

Read only. Returns true or false depending on whether there are more pages before the current page.

Returns the next page of results. Same as calling next_page(). Purely for syntax alone.

Returns the next page of results. If called in list context, returns an array. If called in scalar context, returns a Class::DBI::Lite::Iterator.

If there is not a next page, returns undef.

Returns the previous page of results. If called in list context, returns an array. If called in scalar context, returns a Class::DBI::Lite::Iterator.

If there is not a previous page, returns undef.

OK - grab a cup of coffee, then come back for the explanation.

Ready? Good.

Say you have a $pager:

  my $pager = app::album->pager(undef, {
    page_size => 10,
    page_number => 1,
  });

Then you want to make your paging navigation with at least 10 pages shown, and a maximum of 5 pages to either side of the "current" page (like Google).

  1  2  3  4  5  6  7  8  9  10 11

On the first page you could just do:

  for( 1..10 ) {
    # print a link to that page.
  }

...but...when you get toward the middle or off to the end, it gets weird.

Tah-Dah!

  my ($start, $stop) = $pager->navigations( 5 );

Now you can simply do:

  for( $start..$stop ) {
    # print a link to that page:
  }

It will always do the right thing - will you?

So when you're on page 7 it will look like this:

  2  3  4  5  6  7  8  9  10  11  12

Then, if there were 20 pages in your entire resultset, page 20 would look like this:

  10  11  12  13  14  15  16  17  18  19  20

Great, huh?

Copyright John Drago <jdrago_999@yahoo.com>. All rights reserved.

This software is Free software and may be used and redistributed under the same terms as perl itself.