.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.42)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
.    if \nF \{\
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{\
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\"
.\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2).
.\" Fear.  Run.  Save yourself.  No user-serviceable parts.
.    \" fudge factors for nroff and troff
.if n \{\
.    ds #H 0
.    ds #V .8m
.    ds #F .3m
.    ds #[ \f1
.    ds #] \fP
.\}
.if t \{\
.    ds #H ((1u-(\\\\n(.fu%2u))*.13m)
.    ds #V .6m
.    ds #F 0
.    ds #[ \&
.    ds #] \&
.\}
.    \" simple accents for nroff and troff
.if n \{\
.    ds ' \&
.    ds ` \&
.    ds ^ \&
.    ds , \&
.    ds ~ ~
.    ds /
.\}
.if t \{\
.    ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u"
.    ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u'
.    ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u'
.    ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u'
.    ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u'
.    ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u'
.\}
.    \" troff and (daisy-wheel) nroff accents
.ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V'
.ds 8 \h'\*(#H'\(*b\h'-\*(#H'
.ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#]
.ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H'
.ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u'
.ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#]
.ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#]
.ds ae a\h'-(\w'a'u*4/10)'e
.ds Ae A\h'-(\w'A'u*4/10)'E
.    \" corrections for vroff
.if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u'
.if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u'
.    \" for low resolution devices (crt and lpr)
.if \n(.H>23 .if \n(.V>19 \
\{\
.    ds : e
.    ds 8 ss
.    ds o a
.    ds d- d\h'-1'\(ga
.    ds D- D\h'-1'\(hy
.    ds th \o'bp'
.    ds Th \o'LP'
.    ds ae ae
.    ds Ae AE
.\}
.rm #[ #] #H #V #F C
.\" ========================================================================
.\"
.IX Title "Class::DBI::Plugin::Pager 3pm"
.TH Class::DBI::Plugin::Pager 3pm "2022-10-16" "perl v5.34.0" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
Class::DBI::Plugin::Pager \- paged queries for CDBI
.SH "DESCRIPTION"
.IX Header "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.
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 2
\&    package CD;
\&    use base \*(AqClass::DBI\*(Aq;
\&
\&    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( \*(AqRowsTo\*(Aq );
\&
\&    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
.Ve
.SH "METHODS"
.IX Header "METHODS"
.IP "import" 4
.IX Item "import"
Loads the \f(CW\*(C`pager\*(C'\fR method into the \s-1CDBI\s0 app.
.IP "pager( [$where, [$abstract_attr]], [$order_by], [$per_page], [$page], [$syntax] )" 4
.IX Item "pager( [$where, [$abstract_attr]], [$order_by], [$per_page], [$page], [$syntax] )"
Also accepts named arguments:
.Sp
.Vb 6
\&    where           => $where,
\&    abstract_attr   => $attr,
\&    order_by        => $order_by,
\&    per_page        => $per_page,
\&    page            => $page,
\&    syntax          => $syntax
.Ve
.Sp
Returns a pager object. This subclasses Data::Page.
.Sp
Note that for positional arguments, \f(CW$abstract_attr\fR can only be passed if
preceded by a \f(CW$where\fR argument.
.Sp
\&\f(CW$abstract_attr\fR can contain the \f(CW$order_by\fR setting (just as in
SQL::Abstract).
.RS 4
.IP "configuration" 4
.IX Item "configuration"
The named arguments all exist as get/set methods.
.RS 4
.IP "where" 4
.IX Item "where"
A hashref specifying the query. See SQL::Abstract.
.IP "abstract_attr" 4
.IX Item "abstract_attr"
A hashref specifying extra options to be passed through to the
SQL::Abstract constructor.
.IP "order_by" 4
.IX Item "order_by"
Single column name or arrayref of column names for the \s-1ORDER BY\s0 clause.
Defaults to the primary key(s) if not set.
.IP "per_page" 4
.IX Item "per_page"
Number of results per page.
.IP "page" 4
.IX Item "page"
The pager will retrieve results just for this page. Defaults to 1.
.IP "syntax" 4
.IX Item "syntax"
Change the way the 'limit' clause is constructed. See \f(CW\*(C`set_syntax\*(C'\fR. Default
is \f(CW\*(C`LimitOffset\*(C'\fR.
.RE
.RS 4
.RE
.RE
.RS 4
.RE
.IP "search_where" 4
.IX Item "search_where"
Retrieves results from the pager. Accepts the same arguments as the \f(CW\*(C`pager\*(C'\fR
method.
.IP "retrieve_all" 4
.IX Item "retrieve_all"
Convenience method, generates a \s-1WHERE\s0 clause that matches all rows from the table.
.Sp
Accepts the same arguments as the \f(CW\*(C`pager\*(C'\fR or \f(CW\*(C`search_where\*(C'\fR methods, except that no 
\&\s-1WHERE\s0 clause should be specified.
.Sp
Note that the argument parsing routine called by the \f(CW\*(C`pager\*(C'\fR method cannot cope with 
positional arguments that lack a \s-1WHERE\s0 clause, so either use named arguments, or the 
\&'bit by bit' approach, or pass the arguments directly to \f(CW\*(C`retrieve_all\*(C'\fR.
.ie n .IP "set_syntax( [ $name || $class || $coderef ] )" 4
.el .IP "set_syntax( [ \f(CW$name\fR || \f(CW$class\fR || \f(CW$coderef\fR ] )" 4
.IX Item "set_syntax( [ $name || $class || $coderef ] )"
Changes the syntax used to generate the \f(CW\*(C`limit\*(C'\fR or other phrase that restricts
the results set to the required page.
.Sp
The syntax is implemented as a method called on the pager, which can be
queried to provide the \f(CW$rows\fR and \f(CW$offset\fR parameters (see the subclasses
included in this distribution).
.RS 4
.ie n .IP "$class" 4
.el .IP "\f(CW$class\fR" 4
.IX Item "$class"
A class with a \f(CW\*(C`make_limit\*(C'\fR method.
.ie n .IP "$name" 4
.el .IP "\f(CW$name\fR" 4
.IX Item "$name"
Name of a class in the \f(CW\*(C`Class::DBI::Plugin::Pager::\*(C'\fR namespace, which has a
\&\f(CW\*(C`make_limit\*(C'\fR method.
.ie n .IP "$coderef" 4
.el .IP "\f(CW$coderef\fR" 4
.IX Item "$coderef"
Will be called as a method on the pager object, so receives the pager as its
argument.
.IP "(no args)" 4
.IX Item "(no args)"
Called without args, will default to \f(CW\*(C`LimitOffset\*(C'\fR, which causes
Class::DBI::Plugin::Pager::LimitOffset
to be used.
.RE
.RS 4
.RE
.IP "auto_set_syntax" 4
.IX Item "auto_set_syntax"
This is called automatically when you call \f(CW\*(C`pager\*(C'\fR, and attempts to set the
syntax automatically.
.Sp
If you are using a subclass of the pager, this method will not be called.
.Sp
Will \f(CW\*(C`die\*(C'\fR if using Oracle or \s-1DB2,\s0 since there is no simple syntax for limiting
the results set. \s-1DB2\s0 has a \f(CW\*(C`FETCH\*(C'\fR 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.
.Sp
Supports the following drivers:
.Sp
.Vb 8
\&                      DRIVER        CDBI::P::Pager subclass
\&    my %supported = ( pg        => \*(AqLimitOffset\*(Aq,
\&                      mysql     => \*(AqLimitOffset\*(Aq, # older versions need LimitXY
\&                      sqlite    => \*(AqLimitOffset\*(Aq, # or LimitYX
\&                      sqlite2   => \*(AqLimitOffset\*(Aq, # or LimitYX
\&                      interbase => \*(AqRowsTo\*(Aq,
\&                      firebird  => \*(AqRowsTo\*(Aq,
\&                      );
.Ve
.Sp
Older versions of MySQL should use the LimitXY syntax. You'll need to set it
manually, either by \f(CW\*(C`use CDBI::P::Pager::LimitXY\*(C'\fR, or by passing
\&\f(CW\*(C`syntax => \*(AqLimitXY\*(Aq\*(C'\fR to a method call, or call \f(CW\*(C`set_syntax\*(C'\fR directly.
.Sp
Any driver not in the supported or unsupported lists defaults to LimitOffset.
.Sp
Any additions to the supported and unsupported lists gratefully received.
.SS "\s-1SUBCLASSING\s0"
.IX Subsection "SUBCLASSING"
The 'limit' syntax can be set by using a subclass, e.g.
.PP
.Vb 1
\&    use Class::DBI::Plugin::Pager::RowsTo;
.Ve
.PP
instead of setting at runtime. A subclass looks like this:
.PP
.Vb 2
\&    package Class::DBI::Plugin::Pager::RowsTo;
\&    use base \*(AqClass::DBI::Plugin::Pager\*(Aq;
\&
\&    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;
.Ve
.PP
You can omit the \f(CW\*(C`use base\*(C'\fR and switch syntax by calling
\&\f(CW\*(C`$pager\->set_syntax( \*(AqRowsTo\*(Aq )\*(C'\fR. Or you can leave in the \f(CW\*(C`use base\*(C'\fR and
still say \f(CW\*(C`$pager\->set_syntax( \*(AqRowsTo\*(Aq )\*(C'\fR, because in this case the class is
\&\f(CW\*(C`require\*(C'\fRd and the \f(CW\*(C`import\*(C'\fR in the base class doesn't get called. Or something.
At any rate, It Works.
.PP
The subclasses implement the following \s-1LIMIT\s0 syntaxes:
.IP "Class::DBI::Plugin::Pager::LimitOffset" 4
.IX Item "Class::DBI::Plugin::Pager::LimitOffset"
.Vb 1
\&    LIMIT $rows OFFSET $offset
.Ve
.Sp
This is the default if your driver is not in the list of known drivers.
.Sp
This should work for PostgreSQL, more recent MySQL, SQLite, and maybe some
others.
.IP "Class::DBI::Plugin::LimitXY" 4
.IX Item "Class::DBI::Plugin::LimitXY"
.Vb 1
\&    LIMIT $offset, $rows
.Ve
.Sp
Older versions of MySQL.
.IP "Class::DBI::Plugin::LimitYX" 4
.IX Item "Class::DBI::Plugin::LimitYX"
.Vb 1
\&    LIMIT $rows, $offset
.Ve
.Sp
SQLite.
.IP "Class::DBI::Plugin::RowsTo" 4
.IX Item "Class::DBI::Plugin::RowsTo"
.Vb 1
\&    ROWS $offset TO $offset + $rows
.Ve
.Sp
InterBase, also FireBird, maybe others?
.SH "TODO"
.IX Header "TODO"
I've only used this on an older version of MySQL. Reports of this thing
working (or not) elsewhere would be useful.
.PP
It should be possible to use \f(CW\*(C`set_sql\*(C'\fR to build the complex queries
required by some databases to emulate \s-1LIMIT\s0 (see notes in source).
.SH "CAVEATS"
.IX Header "CAVEATS"
This class can't implement the subselect mechanism required by some databases
to emulate the \s-1LIMIT\s0 phrase, because it only has access to the \s-1WHERE\s0 clause,
not the whole \s-1SQL\s0 statement. At the moment.
.PP
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.
.PP
The \f(CW\*(C`order_by\*(C'\fR 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.
.SH "SOURCE CODE"
.IX Header "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!
.SH "DEPENDENCIES"
.IX Header "DEPENDENCIES"
SQL::Abstract,
Data::Page,
Class::DBI::Plugin::AbstractCount,
Class::Accessor,
Class::Data::Inheritable,
Carp.
.SH "SEE ALSO"
.IX Header "SEE ALSO"
Class::DBI::Pager does a similar job, but retrieves
the entire results set into memory before chopping out the page you want.
.SH "BUGS"
.IX Header "BUGS"
Please report all bugs via the \s-1CPAN\s0 Request Tracker at
<http://rt.cpan.org/NoAuth/Bugs.html?Dist=Class\-DBI\-Plugin\-Pager>.
.SH "COPYRIGHT AND LICENSE"
.IX Header "COPYRIGHT AND LICENSE"
Copyright 2004\-2012 by David Baird.
.PP
Copyright 2012 Nikolay S. \f(CW\*(C`majestic@cpan.org\*(C'\fR
.PP
This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself.
.SH "AUTHOR"
.IX Header "AUTHOR"
David Baird