README

Lampager 核心库

Lampager的核心包

要求

• PHP: ^5.6 || ^7.0 || ^8.0

安装

composer require lampager/lampager

使用方法

基本上您不需要直接使用此包。例如，如果您使用Laravel，请安装 lampager/lampager-laravel。

但是，您可以手动使用如下：

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);

它将执行优化的查询。

(

    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

)

然后您将获得：

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)
  }
}

问题：关于 元组比较 呢？

此功能对于SQL语句应该更简单。然而，根据 SQL功能比较，某些RDBMS，如SQLServer，不支持此语法。因此，Lampager持续使用冗余语句。

它对 Doctrine 2 也很有用，因为它的DQL词法分析器不支持此语法并触发解析错误。

类

API

Paginator::orderBy()
Paginator::orderByDesc()
Paginator::clearOrderBy()

为ORDER BY语句添加或清除光标参数名称。
至少需要一个参数。

Paginator::orderBy(string $column, string $direction = 'asc'): $this
Paginator::orderByDesc(string $column): $this
Paginator::clearOrderBy(): $this

重要：最后一个键必须是主键。

例如：$paginator->orderBy('updated_at')->orderBy('id')

参数

• (string) $column
  表列名。
• (string) $direction
  "asc" 或 "desc"。

Paginator::limit()

定义分页限制。

Paginator::limit(int $limit): $this

参数

• (int) $limit
  正整数。

Paginator::forward()
Paginator::backward()

定义分页方向。

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

重要：您需要上一个光标来检索更多结果。

Paginator::inclusive()
Paginator::exclusive()

Paginator::inclusive(bool $inclusive = true): $this
Paginator::exclusive(bool $exclusive = true): $this

更改处理光标的行为。

包含（默认）

当前光标将被包含在当前页中。

    ===============>
[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

排除

当前光标不会被包含在当前页中。

    ===============>
[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

定义分页结果应同时包含下一个光标和上一个光标。

• unseekable()始终需要一个简单的SELECT查询。（默认）
• seekable()当光标参数不为空时可能需要SELECT ... UNION ALL SELECT ...查询。

Unseekable（默认）

    ===============>
[?] [ 3, 4, 5, 6, 7] [8]
      |               └ next cursor
      └ current cursor

Seekable

    ===============>
[2] [ 3, 4, 5, 6, 7] [8]
 |    |               └ next cursor
 |    └ current cursor
 └ previous cursor

始终在当前光标参数为空时

===============>
[ 1, 2, 3, 4, 5] [6]
                  └ next cursor

Paginator::fromArray()

从关联数组中定义选项。

Paginator::fromArray(array $options): $this

参数

• (array) $options
  包含以下键的关联数组。
  • (int) limit
  • (bool) backward / forward
  • (bool) exclusive / inclusive
  • (bool) seekable / unseekable
  • (string[][]) $orders

例如：

[
    'limit' => 30,
    'backward' => true,
    'unseekable' => false,
    'orders' => [
        ['created_at', 'asc'],
        ['id', 'asc'],
    ],
]

Paginator::configure()

生成对应当前光标的查询。

Paginator::configure(Cursor|array $cursor = []): Query

参数

• (mixed) $cursor
  包含 $column => $value 的关联数组或实现 \Lampager\Contracts\Cursor 的对象。它必须是全有或全无。
  • 对于初始页，省略此参数或传递空数组。
  • 对于后续页面，传递所有参数。不允许部分参数。

AbstractProcessor::process()

接收一对查询和检索到的行以分析和格式化。

AbstractProcessor::process(Query $query, mixed $rows): mixed

参数

• (Query) $query
• (mixed) $rows
  从数据库中检索记录。通常它应该是一个数组或一个可遍历的对象。

返回值

(mixed)

默认情况下，返回 \Lampager\PaginationResult 的一个实例。所有字段都是公开的。

例如：

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)
  }
}

注意：

• hasPrevious/hasNext 在对应方向没有更多结果时将为 false。
• 当 $cursor 为空或 seekable() 没有启用时，hasPrevious/hasNext 将为 null。

PaginationResult::getIterator()

由于实现了 \IteratorAggregate 接口，它可以直接使用 foreach 进行遍历。

AbstractProcessor::getIterator(): \ArrayIterator

返回值

(mixed)

ArrayIterator 实例封装了 records。

AbstractProcessor::useFormatter()
AbstractProcessor::restoreFormatter()

覆盖或恢复分页结果的格式化器。

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);

AbstractProcessor::setDefaultFormatter()
AbstractProcessor::restoreDefaultFormatter()

全局覆盖或恢复格式化器。

static AbstractProcessor::setDefaultFormatter(Formatter|callable $formatter): void
static AbstractProcessor::restoreDefaultFormatter(): void

示例 (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();