Paginating Query Results with CakePHP's Paginator Component

You have a table with thousands of rows and you need to render them in a web page without loading the entire dataset into memory. CakePHP's built-in Paginator component solves this by translating a ?page=2 query string into SQL LIMIT/OFFSET clauses automatically. The useful takeaway: with about ten lines of controller code and one helper call in the template, you get correct, database-level pagination without writing any SQL yourself.

This guide assumes CakePHP 4.x or 5.x with the standard MVC conventions (a controller, an entity/table class, and a template). The behavior described here is stable across those versions.

What you need before starting

• A working CakePHP application with a configured datasource and at least one table with enough rows to span multiple pages.
• A controller for the model you want to paginate (e.g., ArticlesController).
• Optionally, the DebugKit plugin installed so you can inspect the generated SQL later.

Loading the Paginator component

In modern CakePHP, the Paginator is loaded in the controller's initialize() method. Open your controller (for example src/Controller/ArticlesController.php) and add:

public function initialize(): void
{
    parent::initialize();
    $this->loadComponent('Paginator');
}

Alternatively, you can declare default pagination settings as a controller property. This is convenient when several actions share the same page size and sort order:

protected array $paginate = [
    'limit' => 25,
    'order' => ['Articles.created' => 'desc'],
    'maxLimit' => 100,
];

The maxLimit setting caps the limit value a user can pass via the URL (e.g., ?limit=10000). Setting it protects you from a request forcing the database to return an enormous page. Keep limit and maxLimit consistent across actions that list the same data, so users do not see page sizes change unexpectedly between screens.

Paginating in the action

In the action, build a normal query and hand it to paginate():

public function index()
{
    $query = $this->Articles->find()
        ->where(['Articles.published' => true])
        ->contain(['Authors'])
        ->order(['Articles.created' => 'DESC']);

    $articles = $this->paginate($query);
    $this->set(compact('articles'));
}

Key points:

• paginate() reads page, limit, sort, and direction from the request query string and applies them to the query.
• Conditions (where) and eager-loaded associations (contain) work as usual. If you use contain, confirm the association is actually defined on the table class — a missing association can silently produce empty joined data rather than an error.
• You can override settings per call: $this->paginate($query, ['limit' => 10]);.

Rendering navigation in the template

In templates/Articles/index.php, loop over the results and use the Paginator helper (available by default in views) to render controls:

<table>
<?php foreach ($articles as $article): ?>
    <tr>
        <td><?= h($article->title) ?></td>
        <td><?= h($article->author->name ?? '') ?></td>
    </tr>
<?php endforeach; ?>
</table>

<div class="paginator">
    <?= $this->Paginator->prev('« Previous') ?>
    <?= $this->Paginator->numbers() ?>
    <?= $this->Paginator->next('Next »') ?>
    <p><?= $this->Paginator->counter('Page {{page}} of {{pages}}, showing {{current}} of {{count}} total') ?></p>
</div>

numbers() renders clickable page links, and counter() gives users context about where they are in the result set. Sortable column headers can be generated with $this->Paginator->sort('title'), which produces a link that toggles ascending/descending order.

Verifying it actually paginates at the database

Do not assume pagination is efficient just because the page renders. Check these things:

1. Vary the page parameter. Load /articles?page=1 and /articles?page=2 and confirm the records change and the navigation highlights the correct page.
2. Inspect the SQL. With DebugKit enabled, open the SQL log panel and confirm the query contains LIMIT 25 OFFSET 25 (or the equivalent for your page size). If you see the full table being fetched and sliced in PHP, something is bypassing the paginator.
3. Test bad input. Request ?page=abc or a page beyond the last one. CakePHP should handle this gracefully — by default it throws a NotFoundException for out-of-range pages, which renders as a 404. Decide whether that is acceptable for your application or whether you want to catch it and redirect to page 1.
4. Test the limit cap. Request ?limit=99999 and confirm the SQL still uses your maxLimit, not the requested value.

Common problems and recovery

Empty page with no error. Usually a broken contain() or an overly restrictive where. Remove clauses one at a time and check the SQL log to isolate which condition eliminates all rows.

Sorting by a column throws an error or is ignored. The paginator only allows sorting on whitelisted fields. If you use sort() links, define 'sortableFields' => ['title', 'created'] in the paginate settings or pass allowed fields to paginate(). This also prevents users from injecting arbitrary column names into ORDER BY.

Performance degrades on deep pages. Offset pagination (LIMIT 25 OFFSET 5000) forces the database to scan and discard rows. For very large tables, consider keyset (cursor) pagination instead — filtering with WHERE id < :last_seen_id — which the Paginator component does not provide out of the box.

None of these steps modify application state beyond code edits, so recovery is simply reverting your controller/template changes. If you deployed broken pagination, the fix is a code rollback, not a data rollback.