NAME

       Class::DBI::Plugin::Pager - paged queries for CDBI

DESCRIPTION

       Adds a pager method to your class that can query using SQL::Abstract where clauses, and
       limit the number of rows returned to a specific subset.

SYNOPSIS

           package CD;
           use base 'Class::DBI';

           use Class::DBI::Plugin::AbstractCount;      # pager needs this
           use Class::DBI::Plugin::Pager;

           # or to use a different syntax
           # use Class::DBI::Plugin::Pager::RowsTo;

           __PACKAGE__->set_db(...);

           # in a nearby piece of code...

           use CD;

           # see SQL::Abstract for how to specify the query
           my $where = { ... };

           my $order_by => [ qw( foo bar ) ];

           # bit by bit:
           my $pager = CD->pager;

           $pager->per_page( 10 );
           $pager->page( 3 );
           $pager->where( $where );
           $pager->order_by( $order_by );

           $pager->set_syntax( 'RowsTo' );

           my @cds = $pager->search_where;

           # or all at once
           my $pager = CD->pager( $where, $order_by, 10, 3 );

           my @cds = $pager->search_where;

           # or

           my $pager = CD->pager;

           my @cds = $pager->search_where( $where, $order_by, 10, 3 );

           # $pager isa Data::Page
           # @cds contains the CDs just for the current page

METHODS

       import
           Loads the "pager" method into the CDBI app.

       pager( [$where, [$abstract_attr]], [$order_by], [$per_page], [$page], [$syntax] )
           Also accepts named arguments:

               where           => $where,
               abstract_attr   => $attr,
               order_by        => $order_by,
               per_page        => $per_page,
               page            => $page,
               syntax          => $syntax

           Returns a pager object. This subclasses Data::Page.

           Note that for positional arguments, $abstract_attr can only be passed if preceded by a
           $where argument.

           $abstract_attr can contain the $order_by setting (just as in SQL::Abstract).

           configuration
               The named arguments all exist as get/set methods.

               where
                   A hashref specifying the query. See SQL::Abstract.

               abstract_attr
                   A hashref specifying extra options to be passed through to the SQL::Abstract
                   constructor.

               order_by
                   Single column name or arrayref of column names for the ORDER BY clause.
                   Defaults to the primary key(s) if not set.

               per_page
                   Number of results per page.

               page
                   The pager will retrieve results just for this page. Defaults to 1.

               syntax
                   Change the way the 'limit' clause is constructed. See "set_syntax". Default is
                   "LimitOffset".

       search_where
           Retrieves results from the pager. Accepts the same arguments as the "pager" method.

       retrieve_all
           Convenience method, generates a WHERE clause that matches all rows from the table.

           Accepts the same arguments as the "pager" or "search_where" methods, except that no
           WHERE clause should be specified.

           Note that the argument parsing routine called by the "pager" method cannot cope with
           positional arguments that lack a WHERE clause, so either use named arguments, or the
           'bit by bit' approach, or pass the arguments directly to "retrieve_all".

       set_syntax( [ $name || $class || $coderef ] )
           Changes the syntax used to generate the "limit" or other phrase that restricts the
           results set to the required page.

           The syntax is implemented as a method called on the pager, which can be queried to
           provide the $rows and $offset parameters (see the subclasses included in this
           distribution).

           $class
               A class with a "make_limit" method.

           $name
               Name of a class in the "Class::DBI::Plugin::Pager::" namespace, which has a
               "make_limit" method.

           $coderef
               Will be called as a method on the pager object, so receives the pager as its
               argument.

           (no args)
               Called without args, will default to "LimitOffset", which causes
               Class::DBI::Plugin::Pager::LimitOffset to be used.

       auto_set_syntax
           This is called automatically when you call "pager", and attempts to set the syntax
           automatically.

           If you are using a subclass of the pager, this method will not be called.

           Will "die" if using Oracle or DB2, since there is no simple syntax for limiting the
           results set. DB2 has a "FETCH" keyword, but that seems to apply to a cursor and I
           don't know if there is a cursor available to the pager. There should probably be
           others to add to the unsupported list.

           Supports the following drivers:

                                 DRIVER        CDBI::P::Pager subclass
               my %supported = ( pg        => 'LimitOffset',
                                 mysql     => 'LimitOffset', # older versions need LimitXY
                                 sqlite    => 'LimitOffset', # or LimitYX
                                 sqlite2   => 'LimitOffset', # or LimitYX
                                 interbase => 'RowsTo',
                                 firebird  => 'RowsTo',
                                 );

           Older versions of MySQL should use the LimitXY syntax. You'll need to set it manually,
           either by "use CDBI::P::Pager::LimitXY", or by passing "syntax => 'LimitXY'" to a
           method call, or call "set_syntax" directly.

           Any driver not in the supported or unsupported lists defaults to LimitOffset.

           Any additions to the supported and unsupported lists gratefully received.

   SUBCLASSING
       The 'limit' syntax can be set by using a subclass, e.g.

           use Class::DBI::Plugin::Pager::RowsTo;

       instead of setting at runtime. A subclass looks like this:

           package Class::DBI::Plugin::Pager::RowsTo;
           use base 'Class::DBI::Plugin::Pager';

           sub make_limit {
               my ( $self ) = @_;

               my $offset = $self->skipped;
               my $rows   = $self->entries_per_page;

               my $last = $rows + $offset;

               return "ROWS $offset TO $last";
           }

           1;

       You can omit the "use base" and switch syntax by calling "$pager->set_syntax( 'RowsTo' )".
       Or you can leave in the "use base" and still say "$pager->set_syntax( 'RowsTo' )", because
       in this case the class is "require"d and the "import" in the base class doesn't get
       called. Or something.  At any rate, It Works.

       The subclasses implement the following LIMIT syntaxes:

       Class::DBI::Plugin::Pager::LimitOffset
               LIMIT $rows OFFSET $offset

           This is the default if your driver is not in the list of known drivers.

           This should work for PostgreSQL, more recent MySQL, SQLite, and maybe some others.

       Class::DBI::Plugin::LimitXY
               LIMIT $offset, $rows

           Older versions of MySQL.

       Class::DBI::Plugin::LimitYX
               LIMIT $rows, $offset

           SQLite.

       Class::DBI::Plugin::RowsTo
               ROWS $offset TO $offset + $rows

           InterBase, also FireBird, maybe others?

TODO

       I've only used this on an older version of MySQL. Reports of this thing working (or not)
       elsewhere would be useful.

       It should be possible to use "set_sql" to build the complex queries required by some
       databases to emulate LIMIT (see notes in source).

CAVEATS

       This class can't implement the subselect mechanism required by some databases to emulate
       the LIMIT phrase, because it only has access to the WHERE clause, not the whole SQL
       statement. At the moment.

       Each query issues two requests to the database - the first to count the entire result set,
       the second to retrieve the required subset of results. If your tables are small it may be
       quicker to use Class::DBI::Pager.

       The "order_by" clause means the database has to retrieve (internally) and sort the entire
       results set, before chopping out the requested subset. It's probably a good idea to have
       an index on the column(s) used to order the results. For huge tables, this approach to
       paging may be too inefficient.

SOURCE CODE

       The source code for this module is hosted on GitHub
       <https://github.com/majesticcpan/class-dbi-plugin-pager>.  Feel free to fork the
       repository and submit pull requests!

DEPENDENCIES

       SQL::Abstract, Data::Page, Class::DBI::Plugin::AbstractCount, Class::Accessor,
       Class::Data::Inheritable, Carp.

SEE ALSO

       Class::DBI::Pager does a similar job, but retrieves the entire results set into memory
       before chopping out the page you want.

BUGS

       Please report all bugs via the CPAN Request Tracker at
       <http://rt.cpan.org/NoAuth/Bugs.html?Dist=Class-DBI-Plugin-Pager>.

COPYRIGHT AND LICENSE

       Copyright 2004-2012 by David Baird.

       Copyright 2012 Nikolay S. "majestic@cpan.org"

       This library is free software; you can redistribute it and/or modify it under the same
       terms as Perl itself.

AUTHOR

       David Baird