The core package of Lampager
- PHP:
^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)
}
}
Question: How about Tuple Comparison?
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
Table column name.(string)
$direction
"asc"
or"desc"
.
Define the pagination limit.
Paginator::limit(int $limit): $this
(int)
$limit
Positive integer.
Define 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 simpleSELECT
query. (Default)seekable()
may requireSELECT ... 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
Associative array that contains the following keys.(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
An associative array that contains$column => $value
or an object that implements\Lampager\Contracts\Cursor
. It must be all-or-nothing.- For the initial page, omit this parameter or pass an empty array.
- For subsequent pages, pass all parameters. Partial parameters are not allowed.
Receive a pair of Query and fetched rows to analyze and format them.
AbstractProcessor::process(Query $query, mixed $rows): mixed
(Query)
$query(mixed)
$rows
Fetched records from database. Typically it should be an array or a Traversable.
(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 befalse
when there are no more results for the corresponding direction.- Either
hasPrevious
/hasNext
will benull
when$cursor
is empty orseekable()
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();