Lampager for CakePHP

Rapid pagination without using OFFSET

Requirements

PHP: ^8.1
CakePHP: ^5.0
lampager/lampager: ^0.4

Note

For CakePHP 2.x, use lampager/lampager-cakephp2.
For CakePHP 3.x, use lampager/lampager-cakephp v1.x.
For CakePHP 4.x, use lampager/lampager-cakephp v2.x.
For CakePHP 5.x, use lampager/lampager-cakephp v3.x (this version).

Installing

composer require lampager/lampager-cakephp:^3.0

For SQLite users, see SQLite to configure.

Basic Usage

Simply install as a Composer package and use in one or more of the following methods:

Use in Controller (via \Lampager\Cake\Datasource\Paginator)
Use in Table (via \Lampager\Cake\Model\Behavior\LampagerBehavior)

Use in Controller

At first, configure $paginate to use \Lampager\Cake\Datasource\Paginator in your Controller class.

namespace App\Controller;

use Cake\Controller\Controller;
use Lampager\Cake\Datasource\Paginator;

class AppController extends Controller
{
    public $paginate = [
        'className' => Paginator::class,
    ];
}

Use in a way described in the Cookbook: Pagination. Note the options that are specific to Lampager such as forward, seekable, or cursor.

$query = $this->Posts
    ->where(['Posts.type' => 'public'])
    ->orderByDesc('created')
    ->orderByDesc('id')
    ->limit(10);

$posts = $this->paginate($query, [
    'forward' => true,
    'seekable' => true,
    'cursor' => [
        'id' => 4,
        'created' => '2020-01-01 10:00:00',
    ],
]);

$this->set('posts', $posts);

Use in Table

Initialize LampagerBehavior in your Table class (AppTable is preferable) and simply use lampager() there.

namespace App\Model\Table;

use Cake\ORM\Table;
use Lampager\Cake\Model\Behavior\LampagerBehavior;

class AppTable extends Table
{
    public function initialize(array $config): void
    {
        parent::initialize($config);

        $this->addBehavior(LampagerBehavior::class);
    }
}

The query builder (\Lampager\Cake\ORM\Query) extends the plain old \Cake\ORM\Query and is mixed in with \Lampager\Paginator. Note that some of the methods in \Lampager\Paginator, viz., orderBy(), orderByDesc(), and clearOrderBy() are not exposed because their method signatures are not compatible with the CakePHP query builder.

$cursor = [
    'id' => 4,
    'created' => '2020-01-01 10:00:00',
    'modified' => '2020-01-01 12:00:00',
];

/** @var \Lampager\Cake\PaginationResult $latest */
$latest = $this->lampager()
    ->forward()
    ->seekable()
    ->cursor($cursor)
    ->limit(10)
    ->orderByDesc('Posts.modified')
    ->orderByDesc('Posts.created')
    ->orderByDesc('Posts.id')
    ->paginate();

foreach ($latest as $post) {
    /** @var \Cake\ORM\Entity $post */
    debug($post->id);
    debug($post->created);
    debug($post->modified);
}

The methods from the CakePHP query builder, e.g., where(), are available. \Cake\Database\Expression\QueryExpression is accepted as well.

/** @var \Lampager\Cake\PaginationResult $drafts */
$drafts = $this->lampager()
    ->where(['type' => 'draft'])
    ->forward()
    ->seekable()
    ->cursor($cursor)
    ->limit(10)
    ->orderByDesc($this->selectQuery()->newExpr('modified'))
    ->orderByDesc($this->selectQuery()->newExpr('created'))
    ->orderByDesc($this->selectQuery()->newExpr('id'))
    ->paginate();

/** @var \Cake\ORM\Entity $sample */
$sample = $drafts->sample();

/** @var int $count */
$count = $drafts->count();

Classes

API

LampagerBehavior::lampager()

Build a Lampager query from Table in exactly the same way as CakePHP.

LampagerBehavior::lampager(): \Lampager\Cake\ORM\Query

Paginator::__construct()
Paginator::create()

Create a new paginator instance. These methods are not intended to be directly used in your code.

static Paginator::create(\Cake\ORM\Query\SelectQuery $builder): static
Paginator::__construct(\Cake\ORM\Query\SelectQuery $builder)

Paginator::transform()

Transform a Lampager query into a CakePHP query.

Paginator::transform(\Lampager\Query $query): \Cake\ORM\Query\SelectQuery

Paginator::build()

Perform configure + transform.

Paginator::build(\Lampager\Contracts\Cursor|array $cursor = []): \Cake\ORM\Query\SelectQuery

Paginator::paginate()

Perform configure + transform + process.

Paginator::paginate(\Lampager\Contracts\Cursor|array $cursor = []): \Lampager\Cake\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 the initial page, omit this parameter or pass an empty array.
- For the subsequent pages, pass all the parameters. The partial one is not allowed.

Return Value

e.g.,

(Default format when using \Cake\ORM\Query)

object(Lampager\Cake\PaginationResult)#1 (6) {
  ["(help)"]=>
  string(44) "This is a Lampager Pagination Result object."
  ["records"]=>
  array(3) {
    [0]=>
    object(Cake\ORM\Entity)#2 (11) { ... }
    [1]=>
    object(Cake\ORM\Entity)#3 (11) { ... }
    [2]=>
    object(Cake\ORM\Entity)#4 (11) { ... }
  ["hasPrevious"]=>
  bool(false)
  ["previousCursor"]=>
  NULL
  ["hasNext"]=>
  bool(true)
  ["nextCursor"]=>
  array(2) {
    ["created"]=>
    object(Cake\I18n\Time)#5 (3) {
      ["date"]=>
      string(26) "2017-01-01 10:00:00.000000"
      ["timezone_type"]=>
      int(3)
      ["timezone"]=>
      string(3) "UTC"
    }
    ["id"]=>
    int(1)
  }
}

PaginationResult::__call()

\Lampager\Cake\PaginationResult implements \Cake\Datasource\Paging\PaginatedInterface.

Examples

This section describes the practical usage of lampager-cakephp.

Use in Controller

The example below shows how to accept a cursor parameter from a request and pass it through PaginatorComponent::paginate(). Be sure that your AppController has properly initialized Paginator as above.

namespace App\Controller;

class PostsController extends AppController
{
    public $Posts = null;

    /**
     * This method shows how to pass options by a query and array.
     */
    public function query(): void
    {
        // Get cursor parameters
        $previous = json_decode($this->request->getQuery('previous_cursor'), true);
        $next = json_decode($this->request->getQuery('next_cursor'), true);
        $cursor = $previous ?: $next ?: [];

        // Query expression can be passed to PaginatorComponent::paginate() as normal
        $query = $this->Posts
            ->where(['Posts.type' => 'public'])
            ->orderByDesc('created')
            ->orderByDesc('id')
            ->limit(15);

        /** @var \Lampager\Cake\PaginationResult<\Cake\ORM\Entity> $posts */
        $posts = $this->paginate($query, [
            // If the previous_cursor is not set, paginate forward; otherwise backward
            'forward' => !$previous,
            'cursor' => $cursor,
            'seekable' => true,
        ]);

        $this->set('posts', $posts);
    }

    /**
     * This method shows how to pass options from an array.
     */
    public function options(): void
    {
        // Get cursor parameters
        $previous = json_decode($this->request->getQuery('previous_cursor'), true);
        $next = json_decode($this->request->getQuery('next_cursor'), true);
        $cursor = $previous ?: $next ?: [];

        /** @var \Lampager\Cake\PaginationResult<\Cake\ORM\Entity> $posts */
        $posts = $this->paginate('Posts', [
            // Lampager options
            // If the previous_cursor is not set, paginate forward; otherwise backward
            'forward' => !$previous,
            'cursor' => $cursor,
            'seekable' => true,

            // PaginatorComponent config
            'conditions' => [
                'type' => 'public',
            ],
            'order' => [
                'created' => 'DESC',
                'id' => 'DESC',
            ],
            'limit' => 15,
        ]);

        $this->set('posts', $posts);
    }
}

And the pagination links can be output as follows:

// If there is a next page, print pagination link
if ($posts->hasPrevious) {
    echo $this->Html->link('<< Previous', [
        'controller' => 'posts',
        'action' => 'index',
        '?' => [
            'previous_cursor' => json_encode($posts->previousCursor),
        ],
    ]);
}

// If there is a next page, print pagination link
if ($posts->hasNext) {
    echo $this->Html->link('Next >>', [
        'controller' => 'posts',
        'action' => 'index',
        '?' => [
            'next_cursor' => json_encode($posts->nextCursor),
        ],
    ]);
}

Supported database engines

MySQL, MariaDB, and PostgreSQL

Supported!

Microsoft SQL Server

Not supported.

SQLite

Supported but requires an additional configuration.

In SQLite UNION ALL statements cannot combine SELECT statements that have ORDER BY clause. In order to get this to work, those SELECT statements have to be wrapped by a subquery like SELECT * FROM (...). CakePHP not natively handling this situation, Lampager for CakePHP introduces \Lampager\Cake\Database\Driver\Sqlite that needs to be installed on your application. Configure like the following in your config/app.php:

return [
    'Datasources' => [
        'default' => [
            'className' => Connection::class,
            'driver' => \Lampager\Cake\Database\Driver\Sqlite::class,
            'username' => '********',
            'password' => '********',
            'database' => '********',
        ],
    ],
];

Name	Type	Parent Class Implemented Interface	Description
Lampager\Cake\ORM\`Query`	Class	Cake\ORM\`Query`	Fluent factory implementation for CakePHP
Lampager\Cake\Model\Behavior\`LampagerBehavior`	Class	Cake\ORM\`Behavior`	CakePHP behavior which returns Lampager\Cake\ORM\`Query`
Lampager\Cake\Datasource\`Paginator`	Class	Cake\Datasource\`Paginator`	CakePHP paginatior which delegates to Lampager\Cake\ORM\`Query`
Lampager\Cake\`Paginator`	Class	Lampager\`Paginator`	Paginator implementation for CakePHP
Lampager\Cake\`ArrayProcessor`	Class	Lampager\`ArrayProcessor`	Processor implementation for CakePHP
Lampager\Cake\`PaginationResult`	Class	Lampager\`PaginationResult` Cake\Datasource\Paging\`PaginatedInterface`	PaginationResult implementation for CakePHP
Lampager\Cake\Database\`SqliteCompiler`	Class	Cake\Database\`SqliteCompiler`	Query compiler implementation for SQLite
Lampager\Cake\Database\Driver\`Sqlite`	Class	Cake\Database\Driver\`Sqlite`	Driver implementation which delegates to Lampager\Cake\Database\`SqliteCompiler`

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

README.md

README.md

Lampager for CakePHP

Requirements

Note

Installing

Basic Usage

Use in Controller

Use in Table

Classes

API

LampagerBehavior::lampager()

Paginator::__construct()
Paginator::create()

Paginator::transform()

Paginator::build()

Paginator::paginate()

Arguments

Return Value

PaginationResult::__call()

Examples

Use in Controller

Supported database engines

MySQL, MariaDB, and PostgreSQL

Microsoft SQL Server

SQLite

Files

README.md

Latest commit

History

README.md

File metadata and controls

Lampager for CakePHP

Requirements

Note

Installing

Basic Usage

Use in Controller

Use in Table

Classes

API

LampagerBehavior::lampager()

Paginator::__construct()Paginator::create()

Paginator::transform()

Paginator::build()

Paginator::paginate()

Arguments

Return Value

PaginationResult::__call()

Examples

Use in Controller

Supported database engines

MySQL, MariaDB, and PostgreSQL

Microsoft SQL Server

SQLite

Paginator::__construct()
Paginator::create()