NAME

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

SYNOPSIS

Paged Navigation Through Large Datasets

  # 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 Huge Datasets in Small Chunks

  # 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:
  }

DESCRIPTION

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

CAVEAT EMPTOR

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.

CONSTRUCTOR

new( page_number => 1, page_size => 10 )

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

PUBLIC PROPERTIES

page_number

Read-write. Sets/gets the page number.

page_size

Read only. Returns the page size.

total_pages

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

total_items

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

start_item

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

stop_item

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

has_next

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

has_prev

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

PUBLIC METHODS

items( )

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

next_page( )

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.

prev_page( )

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.

navigations( [$padding = 5] )

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?

AUTHOR

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

LICENSE

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

cpanm Class::DBI::Lite

perl -MCPAN -e shell
install Class::DBI::Lite