Server-Side Pagination in CakePHP 4.x with Query::paginate() and Scoped Finders

The problem: paginating filtered lists without hand-rolled LIMIT/OFFSET

You have a controller action that lists records filtered by a finder — say, published articles — and you want page-by-page navigation. Writing your own LIMIT/OFFSET math is error-prone and duplicates what the ORM already does. CakePHP's paginator reads page and limit from the query string, injects the correct LIMIT/OFFSET into the generated SQL, and gives the view helper everything it needs to render links. The takeaway: paginate the query object, not a fetched result set, and clamp the limit parameter before it reaches the database.

What changed between CakePHP 3.x and 4.x

In CakePHP 3.x, pagination lived on the controller: $this->paginate($query). From 4.x onward the paginator moved to the ORM as Cake\ORM\Query::paginate(), and the controller alias is deprecated and scheduled for removal in a future major release. If you are on 4.x or 5.x, call paginate() on the query itself. This guide assumes CakePHP 4.x; verify against your installed version with composer show cakephp/cakephp.

Prerequisites

• A CakePHP 4.x application with a working database connection and a table class (the examples use ArticlesTable).
• A route and template for the listing action.
• The PaginatorHelper loaded in your AppView (it is in the default skeleton's $helpers array).

Procedure: paginate a finder-scoped query

Build the query with your finder conditions first, then call paginate() on it. Run this in the controller action; no special permissions beyond normal application access are needed.

// src/Controller/ArticlesController.php
public function index()
{
    $query = $this->Articles->find()
        ->where(['status' => 'published'])
        ->orderDesc('published_on');

    $articles = $query->paginate($this->request->getQueryParams(), [
        'limit' => 20,
        'maxLimit' => 100,
    ]);

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

Key points:

• The finder conditions (status = 'published') survive pagination — the paginator wraps the existing query rather than replacing it.
• maxLimit clamps the limit GET parameter. Without it, a request like ?limit=100000 forces the database to return that many rows, an easy denial-of-service vector. Keep the cap at something your page can actually render, e.g. 100.
• page values below 1 or beyond the last page raise a NotFoundException by default, which is usually the behavior you want.

In the template (templates/Articles/index.php), render the controls with the helper:

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

The generated links automatically carry page and limit — you do not build URLs by hand.

Verifying the generated SQL

Request the endpoint with explicit parameters, e.g. /articles?page=2&limit=20. Then confirm the ORM produced the right bounds:

• With DebugKit installed, open the SQL panel and check the listing query contains LIMIT 20 OFFSET 20 (page 2 × limit 20).
• Without DebugKit, temporarily enable query logging in config/app_local.php or add Log::debug((string)$query) before paginating.
• Check the rendered page links: hovering the "2" link should show ?page=2&limit=20 preserved.

If OFFSET does not match (page - 1) × limit, the most common cause is passing options to the deprecated controller-level paginate() where they are silently handled differently — move the call onto the query object as shown above.

Limitations and when to paginate in memory

• Server-side pagination issues a COUNT query plus the page query on every request. On very large tables with heavy joins, the count can dominate; consider caching the count or using simple prev/next without total pages.
• For small, already-fetched datasets (a few hundred rows), slicing an in-memory collection and passing it to the view is simpler and avoids the extra queries — but you lose the helper's automatic URL generation for the SQL-backed flow.
• Sorting via sort query params only works for whitelisted fields configured in the paginator's sortableFields; never let arbitrary column names reach ORDER BY.

Nothing here mutates state, so there is no rollback beyond removing the code. The one operational risk is exposing the endpoint without maxLimit — fix that before shipping.