NAME

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

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,
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.

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( $where, [ $attr ] )

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

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.

$class

A class with a make_limit method. Accepts $offset, $rows arguments. Returns an SQL phrase that will be added to the end of the WHERE clause.

$name

Name of a class in the Class::DBI::Plugin::Pager:: namespace, which has a make_limit method as above.

$coderef

Accepts arguments $offset, $rows. This will be called as a method on the pager, so it will also receive the pager as a first 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 will not get 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', # LimitXY should also work
                  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.

I don't know if there are drivers for InterBase or FireBird, but if there are, they use the ROWS .. TO .. syntax and it should work.

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 required and the import in the base class doesn't get called.

The subclasses implement the following LIMIT syntaxes:

Class::DBI::Plugin::Pager::LimitOffset

LIMIT $rows OFFSET $offset

This is the default i.e. you can just say use Class::DBI::Plugin::Pager to get this syntax.

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

Class::DBI::Plugin::LimitXY

LIMIT $offset, $rows

I think this syntax is only used by older versions of MySQL.

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.

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.

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 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.

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 by David Baird.

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

AUTHOR

David Baird, cpan@riverside-cms.co.uk