Skip to content

lampager/lampager-idiorm

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

21 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

lampager-idiorm

Build Status Coverage Status Scrutinizer Code Quality

Lampager for Idiorm and Paris

Rapid pagination without using OFFSET

Requirements

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.

Name Type Parent Class Description
Lampager\Idiorm\Paginator Class Lampager\Paginator Fluent factory implementation for Idiorm and Paris
Lampager\Idiorm\Processor Class Lampager\AbstractProcessor Processor implementation for Idiorm and Paris
Lampager\Idiorm\PaginationResult Class Lampager\PaginationResult PaginationResult implementation for Idiorm and Paris
  • All camelCase methods in Paginator, Processor and PaginationResult 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