lampager / lampager-idiorm
Rapid pagination for Idiorm and Paris
Requires
- php: >=5.6
- ext-json: *
- lampager/lampager: ^0.4
Requires (Dev)
- j4mie/idiorm: ^1.5
- j4mie/paris: ^1.5
- nilportugues/sql-query-formatter: ^1.2.2
- php-coveralls/php-coveralls: ^1.0
- phpunit/phpunit: ^6.4
README
Lampager for Idiorm and Paris
Rapid pagination without using OFFSET
Requirements
- PHP:
>=5.6
- j4mie/idiorm:
^5.4
, j4mie/paris:^1.5
- lampager/lampager:
^0.4
Installing
composer require lampager/lampager-idiorm
Basic Usage
You can wrap ORM
instance with global function lampager()
to make it chainable.
$cursor = [ 'id' => 3, 'created_at' => '2017-01-10 00:00:00', 'updated_at' => '2017-01-20 00:00:00', ]; $result = lampager(ORM::for_table('posts')->where_equal('user_id', 1)) ->forward() ->limit(5) ->order_by_desc('updated_at') // ORDER BY `updated_at` DESC, `created_at` DESC, `id` DESC ->order_by_desc('created_at') ->order_by_desc('id') ->seekable() ->paginate($cursor) ->to_json(JSON_PRETTY_PRINT);
It will run the optimized query.
SELECT * FROM ( 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 ) `temporary_table` UNION ALL SELECT * FROM ( 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 ) `temporary_table`
And you'll get
{ "records": [ { "id": 3, "user_id": 1, "text": "foo", "created_at": "2017-01-10 00:00:00", "updated_at": "2017-01-20 00:00:00" }, { "id": 5, "user_id": 1, "text": "bar", "created_at": "2017-01-05 00:00:00", "updated_at": "2017-01-20 00:00:00" }, { "id": 4, "user_id": 1, "text": "baz", "created_at": "2017-01-05 00:00:00", "updated_at": "2017-01-20 00:00:00" }, { "id": 2, "user_id": 1, "text": "qux", "created_at": "2017-01-17 00:00:00", "updated_at": "2017-01-18 00:00:00" }, { "id": 1, "user_id": 1, "text": "quux", "created_at": "2017-01-16 00:00:00", "updated_at": "2017-01-18 00:00:00" } ], "has_previous": false, "previous_cursor": null, "has_next": true, "next_cursor": { "updated_at": "2017-01-18 00:00:00", "created_at": "2017-01-14 00:00:00", "id": 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.
Classes
Note: See also lampager/lampager.
- All camelCase methods in
Paginator
,Processor
andPaginationResult
can be invoked by snake_case style.
API
Note: See also lampager/lampager.
Paginator::__construct()
Paginator::create()
Create a new paginator instance.
If you use global function lampager()
, however, you don't need to directly instantiate.
static Paginator create(\ORM|\ORMWrapper $builder): static Paginator::__construct(\ORM|\ORMWrapper $builder)
Paginator::transform()
Transform Lampager Query into Illuminate builder.
Paginator::transform(\Lampager\Query $query): \ORM|\ORMWrapper
Paginator::build()
Perform configure + transform.
Paginator::build(\Lampager\Contracts\Cursor|array $cursor = []): \ORM|\ORMWrapper
Paginator::paginate()
Perform configure + transform + process.
Paginator::paginate(\Lampager\Contracts\Cursor|array $cursor = []): \Lampager\idiorm\PaginationResult
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 initial page, omit this parameter or pass empty array.
- For subsequent pages, pass all parameters. Partial parameters are not allowd.
Return Value
e.g.
object(Lampager\Idiorm\PaginationResult)#1 (5) { ["records"]=> array(5) { [0]=> object(ORM)#2 (22) { ... } [1]=> object(ORM)#3 (22) { ... } [2]=> object(ORM)#4 (22) { ... } [3]=> object(ORM)#5 (22) { ... } [4]=> object(ORM)#6 (22) { ... } } ["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) } }
Paginator::useFormatter()
Paginator::restoreFormatter()
Paginator::process()
Invoke Processor methods.
Paginator::useFormatter(\Lampager\Formatter|callable $formatter): $this Paginator::restoreFormatter(): $this Paginator::process(\Lampager\Query $query, array|\IdiormResultSet $rows): \Lampager\idiorm\PaginationResult
PaginationResult::toArray()
PaginationResult::jsonSerialize()
Convert the object into array.
IMPORTANT: camelCase properties are converted into snake_case form.
PaginationResult::toArray(): array PaginationResult::jsonSerialize(): array
PaginationResult::__call()
Call IdiormResultSet
methods.
PaginationResult::__call(string $name, array $args): mixed