Pagination of multiple queries in CakePHP
A less typical use case for pagination in an appication is the need to paginate multiples queries.
In CakePHP you can achieve this with pagination scopes.
Users list
Lest use as an example a simple users list.
// src/Controller/UsersController.php
class UsersController extends AppController
{
protected array $paginate = [
'limit' => 25,
];
public function index()
{
// Default model pagination
$this->set('users', $this->paginate($this->Users));
}
}
// templates/Users/index.php
<h2><?= __('Users list') ?>/h2>
<table>
<thead>
<tr>
<th><?= $this->Paginator->sort('name', __('Name')) ?></th>
<th><?= $this->Paginator->sort('email', __('Email')) ?></th>
<th><?= $this->Paginator->sort('active', __('Active')) ?></th>
</tr>
</thead>
<tbody>
<?php foreach ($users as $user): ?>
<tr>
<td><?= h($user->name) ?></td>
<td><?= h($user->email) ?></td>
<td><?= $user->active ? 'Yes' : 'No' ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?= $this->Paginator->counter() ?>
<?= $this->Paginator->prev('« Previous') ?>
<?= $this->Paginator->numbers() ?>
<?= $this->Paginator->next('Next »') ?>
Pagination of multiple queries
Now, we want to display two paginated tables, one with the active users and the other with the inactive ones.
// src/Controller/UsersController.php
class UsersController extends AppController
{
protected array $paginate = [
'Users' => [
'scope' => 'active_users',
'limit' => 25,
],
'InactiveUsers' => [
'scope' => 'inactive_users',
'limit' => 10,
],
];
public function index()
{
$activeUsers = $this->paginate(
$this->Users->find()->where(['active' => true]),
[scope: 'active_users']
);
// Load an additional table object with the custom alias set in the paginate property
$inactiveUsersTable = $this->fetchTable('InactiveUsers', [
'className' => \App\Model\Table\UsersTable::class,
'table' => 'users',
'entityClass' => 'App\Model\Entity\User',
]);
$inactiveUsers = $this->paginate(
$inactiveUsersTable->find()->where(['active' => false]),
[scope: 'inactive_users']
);
$this->set(compact('users', 'inactiveUsers'));
}
}
// templates/Users/index.php
<?php
// call `setPaginated` first with the results to be displayed next, so the paginator use the correct scope for the links
$this->Paginator->setPaginated($users);
?>
<h2><?= __('Active Users') ?>/h2>
<table>
<thead>
<tr>
<th><?= $this->Paginator->sort('name', __('Name')) ?></th>
<th><?= $this->Paginator->sort('email', __('Email')) ?></th>
<th><?= $this->Paginator->sort('active', __('Active')) ?></th>
</tr>
</thead>
<tbody>
<?php foreach ($users as $user): ?>
<tr>
<td><?= h($user->name) ?></td>
<td><?= h($user->email) ?></td>
<td><?= $user->active ? 'Yes' : 'No' ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?= $this->Paginator->counter() ?>
<?= $this->Paginator->prev('« Previous') ?>
<?= $this->Paginator->numbers() ?>
<?= $this->Paginator->next('Next »') ?>
<?php
// call `setPaginated` first with the results to be displayed next, so the paginator use the correct scope for the links
$this->Paginator->setPaginated($inactiveUsers);
?>
<h2><?= __('Inactive Users') ?>/h2>
<table>
<thead>
<tr>
<th><?= $this->Paginator->sort('name', __('Name')) ?></th>
<th><?= $this->Paginator->sort('email', __('Email')) ?></th>
<th><?= $this->Paginator->sort('active', __('Active')) ?></th>
</tr>
</thead>
<tbody>
<?php foreach ($inactiveUsers as $inactiveUser): ?>
<tr>
<td><?= h($inactiveUser->name) ?></td>
<td><?= h($inactiveUser->email) ?></td>
<td><?= $inactiveUser->active ? 'Yes' : 'No' ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?= $this->Paginator->counter() ?>
<?= $this->Paginator->prev('« Previous') ?>
<?= $this->Paginator->numbers() ?>
<?= $this->Paginator->next('Next »') ?>
And with this you have two paginated tables in the same request.