Class:...::Pager(3pm)

📦

version 0.566 · 3 versions

0.566 · ubuntu noble, ubuntu resolute, ubuntu stonking (latest)
0.566 · ubuntu jammy
0.566 · debian bookworm, debian trixie

, in ubuntu noble, ubuntu resolute, ubuntu stonking

✨ NAME ✨

Class::DBI::Plugin::Pager — paged queries for CDBI 💖

(yeah bestie, this is the Class:...::Pager(3pm) page, straight out of the User Contributed Perl Documentation collection 🫶)

🌸 DESCRIPTION 🌸

okay so here's the tea: this adds a pager method to your class, and that method can query using SQL::Abstract where clauses and limit the number of rows returned to a specific subset. That's it. That's the whole vibe. Paging. Iconic behavior, no cap 👑

🦋 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

literally three different ways to do the same thing and every single one of them eats 💅

💜 METHODS 💜

import — Loads the pager method into the CDBI app. that's the whole job, she's a simple girl and she's thriving 🎀
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. ✨

Heads up bestie: for positional arguments, $abstract_attr can only be passed if preceded by a $where argument. no $where first = no $abstract_attr, that's the rule 🙈

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

🎀 configuration 🎀

The named arguments all exist as get/set methods. every single one of them gets to be its own little icon 💖

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. simple. clean. efficient. we love her 🫶
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.

okay so about that argument parsing — the 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. pick your fighter.

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

💀 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 — the pairing is literally 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. (fr fr, they want your PRs 🥺)

🌈 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. Or something. At any rate, It Works. 😌 (that's the most honest sentence in this whole doc and I respect it)

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. 👀 (yes they're asking for your field reports, bestie, this is your moment)
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 🔥

listen. this part matters, so pay attention 💅

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.

seriously bestie, for huge tables this is giving 💀 — index those order columns.

🧚 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! 🌈 (slay, go get that green square)

🌸 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. (not the same vibe, she eats more RAM, but she's valid too 🫶)

🐛 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 👑

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

💖 AUTHOR 💖

David Baird

and that's the whole page, bestie — perl v5.36.0, dated 2022-10-16, Class:...::Pager(3pm), mwah 💅✨ 🎀

📦 where this page comes from

ubuntu noble: libclass-dbi-plugin-pager-perl 0.566-4 · Artistic-1.0-Perl OR GPL-1.0-or-later
ubuntu resolute: libclass-dbi-plugin-pager-perl 0.566-4 · Artistic-1.0-Perl OR GPL-1.0-or-later
ubuntu stonking: libclass-dbi-plugin-pager-perl 0.566-4 · Artistic-1.0-Perl OR GPL-1.0-or-later
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on 2026-11-03: a language model's rewrite of the original, under its terms