kickstarting a paged result set

Let's say that you want to retrieve the records from a table, via query like:

 
SELECT * FROM customer $LIMIT_CLAUSE
[download]

Now, assuming this returns more records than you would want to display on a single page, then a module like Data::Page or Data::Pageset is useful, because it calculates all the things you need:

the number of pages in the resultset
the number of records to skip for the current SELECT statement
the number of the first and last record in the set

Now, initializing a Data::Page object requires telling it the total number of entries. Now once you have your paged data, you can setup the links to each page so that Data::Page can calculate the proper parameters for your LIMIT clause.

BUT! On the first request for the result set, you do not know the total number of entries. So, you have to get them in addition to the first page of data.

The way that I did this was setup my model class so that it conditionally defined $LIMIT_CLAUSE. If it received a $pager object, then it performed a limited select. Otherwise, it did a full select:

sub all {

    my ($app, $pager) = @_;

    my $LIMIT_CLAUSE;

    if ($pager) {
    $LIMIT_CLAUSE 
      = sprintf "LIMIT %d, %d", $pager->skipped, $pager->entries_per_p
+age;
    }


    my $query =<<"EOSQL";

SELECT * FROM table_name
$LIMIT_CLAUSE

EOSQL
[download]

and I called the Model from a CGI::Application controller like so:

sub affil {
    my $app = shift;

    my $aff_model;
    my $pager = Data::Page->new;

    my $entries_per_page = 2; 
    $pager->entries_per_page($entries_per_page);

    # either call database to get all rows or use CGI query parm
    my $total_entries = 
      $app->query->param('total_entries') 
      || Model::Aff::all($app)->rows ;
    $pager->total_entries($total_entries);

    my $current_page =  $app->query->param('current_page') || 1 ;
    $pager->current_page($current_page);


    $aff_model = Model::Aff::all($app, $pager);
    View::Aff::Main::render($app, $aff_model, $pager);

}
[download]

It seems wasteful to do 2 queries in a row that way, but hopefully database caching will reduce that penalty to just the first time it occurs?

The other option was to use the non-paged resultset (the first time the page was hit without knowledge of total rows in dataset) and modify the View code such that it rendered differently based on whether or not the results were paged.

But that would have been more tedious.

Comment on kickstarting a paged result set Select or Download Code

Replies are listed 'Best First'.
Re: kickstarting a paged result set by mr_mischief (Monsignor) on Feb 03, 2009 at 22:56 UTC
You don't need to return all rows to find out how many rows there are. What's wrong with COUNT()? It's useful, it's handy, and it's standard. It returns one nice number that tells you how many items matched your query: `SELECT COUNT(*) FROM tablename;` [download] One can also count by distinct values in a column, in case you don't want to show duplicates more than once. This would, in a hypothetical monthly log of purchases on an ecommerce site, tell you how many different customers made purchases no matter how many items they bought or how many separate times they checked out: `SELECT COUNT(DISTINCT customer_id) FROM purchases;` [download] When you're working with both Perl and some other language, it helps to see if the other language addresses your needs. In this case, SQL could save your server quite a bit of data transfer and memory usage.	[reply] [d/l] [select]
Re: kickstarting a paged result set by CountZero (Bishop) on Feb 04, 2009 at 06:42 UTC
Modules like DBIx::Class do this "behind the scenes" for you through the use of their `$resultset->page($page_number)` method. Incidentally it indeed uses Data::Page. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply] [d/l]
Re: kickstarting a paged result set by Anonymous Monk on Feb 07, 2009 at 02:28 UTC
If you're using MySQL, there's a nice SQL_CALC_FOUND_ROWS additional option to SELECT. It counts the rows that would be returned without LIMIT. It can be used like this: `SELECT col1, col2, col3 FROM a_table LIMIT 0, 10 SQL_CALC_FOUND_ROWS; +=> your normal resultset SELECT FOUND_ROWS(); => total number of rows returned` [download]	[reply] [d/l]