Tame CakePHP List Endpoints with Explicit Pagination and Contain

The unbounded index action

An index action that calls find('all') and returns every row is a slow-motion outage. Add contain() to load associated records and the problem compounds: one query becomes several, and the response grows with the table. In CakePHP, the reliable fix is not a hand-written LIMIT. It is to build a single object-relational mapper (ORM) query, load only the associations the template actually displays, and pass that query to the controller's paginate() method with explicit caps.

Build one query, then paginate it

In CakePHP 4.x and 5.x, controllers expose a paginate() method that accepts an ORM query directly. You do not need to load a separate paginator component or rely on a controller property. The pattern looks like this:

$query = $this->Articles->find();
$articles = $this->paginate($query, [
    'limit' => 20,
    'maxLimit' => 50,
]);

Passing settings in the paginate() call keeps the behavior visible at the call site. A reader can see the page size cap without hunting through controller state.

Contain only what the template needs

contain() tells the ORM to eager-load associated records. It does not filter the primary table. If your template shows an article title, the author name, and a list of tag titles, contain those associations and select only the columns you render:

$articles = $this->paginate($query, [
    'limit' => 20,
    'maxLimit' => 50,
    'order' => ['Articles.created' => 'desc'],
    'sortableFields' => ['title', 'created', 'modified'],
    'contain' => [
        'Authors' => function (Query $q) {
            return $q->select(['id', 'name']);
        },
        'Tags' => function (Query $q) {
            return $q->select(['id', 'title']);
        },
    ],
]);

If the filter itself depends on associated rows—for example, "articles that have a tag named security"—use innerJoinWith() or matching() deliberately. contain() will not restrict the result set. Be careful with matching() on hasMany or belongsToMany associations: it can multiply rows and distort the pagination count.

Treat query-string parameters as untrusted

Pagination controls read ?page, ?limit, and ?sort from the request. Those values are user input. Two settings turn them into safe, bounded behavior:

• maxLimit caps the requested page size. A request for ?limit=10000 returns at most the value you set.
• sortableFields whitelists sortable columns. A request for ?sort=secret_column falls back to your default order instead of reaching the database.

Always set a default order as well. Without it, the database may return rows in an arbitrary order, and pagination controls become unpredictable. The Paginator view helper generates sortable links and page controls, but it is presentation only. The server-side paginate() settings are the enforcement point; do not pass raw request query values into find() without validation.

Trade-offs and how to verify

Explicit pagination removes the unbounded-response risk, but it does not make every query cheap. Deep contain() chains can still issue multiple queries and large IN lists. A page of 20 articles with three contained associations may execute four queries under the default select strategy: one for the articles, one for the count, and one per association. Measure the actual SQL rather than assuming a single round-trip.

Sorting by an associated or virtual field may require an explicit field alias and may not work through the helper's generated links. Version sensitivity also matters: CakePHP 3.x used a PaginatorComponent and a $paginate property, while current 4.x/5.x controllers call paginate() directly with an ORM query. Confirm option names against your installed minor release.

You can verify the behavior without guessing:

1. Run composer show cakephp/cakephp and bin/cake --version in your project root to confirm the installed framework version. You need read access to composer.json and the vendor directory.
2. Seed the table with more rows than your page limit, then request ?page=2 and ?limit=999. Confirm the response contains the capped number of rows and the correct page offset.
3. Enable DebugKit or log queries and inspect the SQL for one page. Expect one main query, one count query, and one query per contained association for simple select-strategy associations.
4. Request a disallowed sort value such as ?sort=password. Confirm the response uses the default order rather than the raw column name.
5. Compare contain() and matching() on the same dataset to see row counts and generated SQL before choosing one.

These checks assume a relational database supported by CakePHP. Non-SQL datasources may behave differently.

The takeaway is simple: an index endpoint should return a bounded, predictable page of data. Build the ORM query explicitly, contain only what the template renders, and let paginate() enforce the limits. That keeps the response fast, the SQL visible, and the query string harmless.