lampager / lampager
Rapid pagination without using OFFSET
Installs: 65 991
Dependents: 5
Suggesters: 0
Security: 0
Stars: 37
Watchers: 7
Forks: 0
Open Issues: 1
Requires
- php: ^5.6 || ^7.0 || ^8.0
Requires (Dev)
- codeception/specify: ^0.4.6
- nilportugues/sql-query-formatter: ^1.2.2
- php-coveralls/php-coveralls: ^1.0
- phpunit/phpunit: ^6.4
README
Lampager Core
The core package of Lampager
Requirements
- PHP:
^5.6 || ^7.0 || ^8.0
Installing
composer require lampager/lampager
Usage
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.
Classes
API
Paginator::orderBy()
Paginator::orderByDesc()
Paginator::clearOrderBy()
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')
Arguments
(string)
$column
Table column name.(string)
$direction
"asc"
or"desc"
.
Paginator::limit()
Define the pagination limit.
Paginator::limit(int $limit): $this
Arguments
(int)
$limit
Positive integer.
Paginator::forward()
Paginator::backward()
Define the pagination direction.
Paginator::forward(bool $forward = true): $this Paginator::backward(bool $backward = true): $this
Forward (Default)
===============>
[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
Backward
<===============
[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()
Paginator::exclusive()
Paginator::inclusive(bool $inclusive = true): $this Paginator::exclusive(bool $exclusive = true): $this
Change the behavior of handling cursor.
Inclusive (Default)
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
Exclusive
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()
Paginator::seekable()
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.
Unseekable (Default)
===============>
[?] [ 3, 4, 5, 6, 7] [8]
| └ next cursor
└ current cursor
Seekable
===============>
[2] [ 3, 4, 5, 6, 7] [8]
| | └ next cursor
| └ current cursor
└ previous cursor
Always when the current cursor parameters are empty
===============>
[ 1, 2, 3, 4, 5] [6]
└ next cursor
Paginator::fromArray()
Define options from an associative array.
Paginator::fromArray(array $options): $this
Arguments
(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'], ], ]
Paginator::configure()
Generate Query corresponding to the current cursor.
Paginator::configure(Cursor|array $cursor = []): Query
Arguments
(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.
AbstractProcessor::process()
Receive a pair of Query and fetched rows to analyze and format them.
AbstractProcessor::process(Query $query, mixed $rows): mixed
Arguments
(Query)
$query(mixed)
$rows
Fetched records from database. Typically it should be an array or a Traversable.
Return Value
(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.
PaginationResult::getIterator()
It can be directly traversed using foreach
thanks to the interface \IteratorAggregate
.
AbstractProcessor::getIterator(): \ArrayIterator
Return Value
(mixed)
ArrayIterator
instance that wraps records
.
AbstractProcessor::useFormatter()
AbstractProcessor::restoreFormatter()
Override or restore the formatter for the pagination result.
AbstractProcessor::useFormatter(Formatter|callable $formatter): $this AbstractProcessor::restoreFormatter(): $this
Callable Formatter Example
<?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);
Class Formatter Example
<?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);
AbstractProcessor::setDefaultFormatter()
AbstractProcessor::restoreDefaultFormatter()
Globally override or restore the formatter.
static AbstractProcessor::setDefaultFormatter(Formatter|callable $formatter): void static AbstractProcessor::restoreDefaultFormatter(): void
Example (Laravel)
<?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();