Rapid pagination for various PHP frameworks
The core package of Lampager
^5.6 || ^7.0 || ^8.0
composer require lampager/lampager
Basically you don't need to directly use this package. For example, if you use Laravel, install lampager/lampager-laravel.
However, you can manually use like this:
use Lampager\Paginator;
use Lampager\ArrayProcessor;
$cursor = [
'id' => 3,
'created_at' => '2017-01-10 00:00:00',
'updated_at' => '2017-01-20 00:00:00',
];
$query = (new Paginator())
->forward()
->limit(5)
->orderByDesc('updated_at') // ORDER BY `updated_at` DESC, `created_at` DESC, `id` DESC
->orderByDesc('created_at')
->orderByDesc('id')
->seekable()
->configure($cursor);
$rows = run_your_query_using_PDO($query); // Note: SQLite3 driver example is bundled in the tests/StubPaginator.php. Please refer to that.
$result = (new ArrayProcessor())->process($query, $rows);
It will run the optimized query.
(
SELECT * FROM `posts`
WHERE `user_id` = 1
AND (
`updated_at` = '2017-01-20 00:00:00' AND `created_at` = '2017-01-10 00:00:00' AND `id` > 3
OR
`updated_at` = '2017-01-20 00:00:00' AND `created_at` > '2017-01-10 00:00:00'
OR
`updated_at` > '2017-01-20 00:00:00'
)
ORDER BY `updated_at` ASC, `created_at` ASC, `id` ASC
LIMIT 1
) UNION ALL (
SELECT * FROM `posts`
WHERE `user_id` = 1
AND (
`updated_at` = '2017-01-20 00:00:00' AND `created_at` = '2017-01-10 00:00:00' AND `id` <= 3
OR
`updated_at` = '2017-01-20 00:00:00' AND `created_at` < '2017-01-10 00:00:00'
OR
`updated_at` < '2017-01-20 00:00:00'
)
ORDER BY `updated_at` DESC, `created_at` DESC, `id` DESC
LIMIT 6
)
And you'll get
object(Lampager\PaginationResult)#1 (5) {
["records"]=>
array(5) {
[0]=>
array(5) {
["id"]=>
int(3)
["user_id"]=>
int(1)
["text"]=>
string(3) "foo"
["created_at"]=>
string(19) "2017-01-10 00:00:00"
["updated_at"]=>
string(19) "2017-01-20 00:00:00"
}
[1]=>
array(5) {
["id"]=>
int(5)
["user_id"]=>
int(1)
["text"]=>
string(3) "bar"
["created_at"]=>
string(19) "2017-01-05 00:00:00"
["updated_at"]=>
string(19) "2017-01-20 00:00:00"
}
[2]=>
array(5) {
["id"]=>
int(4)
["user_id"]=>
int(1)
["text"]=>
string(3) "baz"
["created_at"]=>
string(19) "2017-01-05 00:00:00"
["updated_at"]=>
string(19) "2017-01-20 00:00:00"
}
[3]=>
array(5) {
["id"]=>
int(2)
["user_id"]=>
int(1)
["text"]=>
string(3) "qux"
["created_at"]=>
string(19) "2017-01-17 00:00:00"
["updated_at"]=>
string(19) "2017-01-18 00:00:00"
}
[4]=>
array(5) {
["id"]=>
int(1)
["user_id"]=>
int(1)
["text"]=>
string(3) "quux"
["created_at"]=>
string(19) "2017-01-16 00:00:00"
["updated_at"]=>
string(19) "2017-01-18 00:00:00"
}
}
["hasPrevious"]=>
bool(false)
["previousCursor"]=>
NULL
["hasNext"]=>
bool(true)
["nextCursor"]=>
array(2) {
["updated_at"]=>
string(19) "2017-01-18 00:00:00"
["created_at"]=>
string(19) "2017-01-14 00:00:00"
["id"]=>
int(6)
}
}
With this feature, SQL statements should be simpler. However, according to SQL Feature Comparison, some RDBMS, such as SQLServer, do not support this syntax. Therefore, Lampager continuously uses redundant statements.
It is also useful for Doctrine 2 since its DQL lexer does not support the syntax and triggers parse errors.
Name | Type | Parent Class Implemented Interface |
Description |
---|---|---|---|
Lampager\Paginator |
Class | Fluent factory for building Query | |
Lampager\AbstractProcessor |
Abstract Class | Receive fetched records and format them | |
Lampager\PaginationResult |
Class | Processor wraps result with this by default | |
Lampager\ArrayProcessor |
Class | Lampager\AbstractProcessor |
Simple Processor implementation for pure PDO |
Lampager\ArrayCursor |
Class | Lampager\Contracts\Cursor |
Simple Cursor implementation for pure PDO Arrays are automatically wrapped with this |
Lampager\Query |
Class | SQL configuration container generated by Paginator | |
Lampager\Query\... | Class | Child components of Query | |
Lampager\Contracts\Cursor |
Interface | Indicates parameters for retrieving previous/next records | |
Lampager\Contracts\Formatter |
Interface | Formatter interface pluggable to Processor | |
Lampager\Concerns\HasProcessor |
Trait | Helper for extended Paginator providing convenient accessibility to Processor |
Add or clear cursor parameter name for ORDER BY
statement.
At least one parameter required.
Paginator::orderBy(string $column, string $direction = 'asc'): $this
Paginator::orderByDesc(string $column): $this
Paginator::clearOrderBy(): $this
IMPORTANT: The last key MUST be the primary key.
e.g. $paginator->orderBy('updated_at')->orderBy('id')
(string)
$column(string)
$direction"asc"
or "desc"
.Define the pagination limit.
Paginator::limit(int $limit): $this
(int)
$limitDefine the pagination direction.
Paginator::forward(bool $forward = true): $this
Paginator::backward(bool $backward = true): $this
===============>
[2] [ 3, 4, 5, 6, 7] [8]
| | └ next cursor
| └ current cursor
└ previous cursor
===============>
[8] [ 7, 6, 5, 4, 3] [2]
| | └ next cursor
| └ current cursor
└ previous cursor
<===============
[2] [ 3, 4, 5, 6, 7] [8]
| | └ next cursor
| └ current cursor
└ previous cursor
<===============
[8] [ 7, 6, 5, 4, 3] [2]
| | └ next cursor
| └ current cursor
└ previous cursor
IMPORTANT: You need previous cursor to retrieve more results.
Paginator::inclusive(bool $inclusive = true): $this
Paginator::exclusive(bool $exclusive = true): $this
Change the behavior of handling cursor.
Current cursor will be included in the current page.
===============>
[2] [ 3, 4, 5, 6, 7] [8]
| | └ next cursor
| └ current cursor
└ previous cursor
<===============
[2] [ 3, 4, 5, 6, 7] [8]
| | └ next cursor
| └ current cursor
└ previous cursor
Current cursor will not be included in the current page.
===============>
[2] [ 3, 4, 5, 6, 7] [8]
| └ next cursor
└ current cursor
<===============
[2] [ 3, 4, 5, 6, 7] [8]
| |
| └ current cursor
└ previous cursor
Paginator::unseekable(bool $unseekable = true): $this
Paginator::seekable(bool $seekable = true): $this
Define that the pagination result should contain both of the next cursor and the previous cursor.
unseekable()
always requires one simple SELECT
query. (Default)seekable()
may require SELECT ... UNION ALL SELECT ...
query when the cursor parameters are not empty. ===============>
[?] [ 3, 4, 5, 6, 7] [8]
| └ next cursor
└ current cursor
===============>
[2] [ 3, 4, 5, 6, 7] [8]
| | └ next cursor
| └ current cursor
└ previous cursor
===============>
[ 1, 2, 3, 4, 5] [6]
└ next cursor
Define options from an associative array.
Paginator::fromArray(array $options): $this
(array)
$options(int)
limit
(bool)
backward / forward
(bool)
exclusive / inclusive
(bool)
seekable / unseekable
(string[][])
$orders
e.g.
[
'limit' => 30,
'backward' => true,
'unseekable' => false,
'orders' => [
['created_at', 'asc'],
['id', 'asc'],
],
]
Generate Query corresponding to the current cursor.
Paginator::configure(Cursor|array $cursor = []): Query
(mixed)
$cursor$column => $value
or an object that implements \Lampager\Contracts\Cursor
. It must be all-or-nothing.
Receive a pair of Query and fetched rows to analyze and format them.
AbstractProcessor::process(Query $query, mixed $rows): mixed
(Query)
$query
(mixed)
$rows(mixed)
By default, an instance of \Lampager\PaginationResult
is returned. All fields are public.
e.g.
object(Lampager\PaginationResult)#1 (5) {
["records"]=>
array(5) {
/* ... */
}
["hasPrevious"]=>
bool(false)
["previousCursor"]=>
NULL
["hasNext"]=>
bool(true)
["nextCursor"]=>
array(2) {
["updated_at"]=>
string(19) "2017-01-18 00:00:00"
["created_at"]=>
string(19) "2017-01-14 00:00:00"
["id"]=>
int(6)
}
}
Note that
hasPrevious
/hasNext
will be false
when there are no more results for the corresponding direction.hasPrevious
/hasNext
will be null
when $cursor
is empty or seekable()
is not be enabled.It can be directly traversed using foreach
thanks to the interface \IteratorAggregate
.
AbstractProcessor::getIterator(): \ArrayIterator
(mixed)
ArrayIterator
instance that wraps records
.
Override or restore the formatter for the pagination result.
AbstractProcessor::useFormatter(Formatter|callable $formatter): $this
AbstractProcessor::restoreFormatter(): $this
<?php
use Lampager\Query;
use Lampager\ArrayProcessor;
use Lampager\PaginationResult;
$formatter = function ($rows, array $meta, Query $query) {
// Drop table prefix in meta properties (e.g. "posts.updated_at" -> "updated_at")
foreach (array_filter($meta, 'is_array') as $property => $cursor) {
foreach ($cursor as $column => $field) {
unset($meta[$property][$column]);
$segments = explode('.', $column);
$meta[$property][end($segments)] = $field;
}
}
return new PaginationResult($rows, $meta);
};
$result = (new ArrayProcessor())->useFormatter($formatter)->process($query, $rows);
<?php
use Lampager\Query;
use Lampager\ArrayProcessor;
use Lampager\PaginationResult;
use Lampager\Contracts\Formatter;
class DropTablePrefix implements Formatter
{
public function format($rows, array $meta, Query $query)
{
// Drop table prefix in meta properties (e.g. "posts.updated_at" -> "updated_at")
foreach (array_filter($meta, 'is_array') as $property => $cursor) {
foreach ($cursor as $column => $field) {
unset($meta[$property][$column]);
$segments = explode('.', $column);
$meta[$property][end($segments)] = $field;
}
}
return new PaginationResult($rows, $meta);
}
}
$result = (new ArrayProcessor())->useFormatter(DropTablePrefix::class)->process($query, $rows);
Globally override or restore the formatter.
static AbstractProcessor::setDefaultFormatter(Formatter|callable $formatter): void
static AbstractProcessor::restoreDefaultFormatter(): void
<?php
use Illuminate\Database\Eloquent\Builder;
use Lampager\Query;
use Lampager\Laravel\Processor as IlluminateProcessor;
IlluminateProcessor::setDefaultFormatter(function ($rows, array $meta, Query $query) {
// Note:
// $builder is provided from extended Paginator.
// For example, lampager/lampager-laravel provides QueryBuilder, EloquentBuilder or Relation.
$builder = $query->builder();
switch ($builder instanceof Builder ? $builder->getModel() : null) {
case Post::class:
return (new PostFormatter())->format($rows, $meta, $query);
case Comment::class:
return (new CommentFormatter())->format($rows, $meta, $query);
default:
return new PaginationResult($rows, $meta);
}
});
$posts = Post::lampager()->orderBy('created_at')->orderBy('id')->paginate();
$comments = Comment::lampager()->orderBy('created_at')->orderBy('id')->paginate();