Paginate with Contain in CakePHP Without Triggering N+1 Queries

An index page that lists Articles with their Authors feels fast on an empty database and then slows down as soon as you loop over the results. The slowdown is usually N+1 queries: one query for the paginated list and then an extra query for each row to load the associated Author or Comments. In CakePHP the useful takeaway is to pair paginate() with a selective contain() so the ORM fetches the associations in a bounded set of queries while still applying limit and offset.

Why pagination alone does not fix association loading

Pagination in CakePHP is a first‑class component that works directly with the ORM Query object. It applies limit and offset after conditions are set, and it preserves contain and ordering you add to the query.

Associations are declared in Table classes. BelongsTo, HasMany, HasOne and BelongsToMany define how entities relate and which foreign keys are used. Once declared, contain() is the Query Builder method for eager loading: it tells the ORM to fetch related records alongside the primary query instead of lazy‑loading them in a loop.

Without contain, accessing $article->author inside a view triggers a separate SELECT for each article. With contain, the ORM builds additional queries for the associated tables and hydrates the entities in memory. The cost moves from many small round trips to a few larger ones.

Selective contain with paginate in practice

Assume Articles belongsTo Authors and hasMany Comments. In src/Model/Table/ArticlesTable.php the associations are defined once:

public function initialize(array $config): void
{
    $this->belongsTo('Authors');
    $this->hasMany('Comments');
}

In a controller action you build a query, add a contain, and hand it to paginate. Run this in an app where you have read access to the Articles table and the Paginator component loaded.

$query = $this->Articles->find()
    ->contain([
        'Authors' => function ($q) {
            return $q->select(['id','name']);
        },
        'Comments' => function ($q) {
            return $q->select(['id','article_id','body'])
                     ->orderAsc('created');
        }
    ])
    ->where(['Articles.status' => 'published']);

$articles = $this->paginate($query);

The Paginator applies limit and offset to the Articles query. The ORM executes separate queries for Authors and Comments, then matches them to the paginated Articles. The view can now read $article->author->name and $article->comments without extra queries.

Bake scaffolds a similar pattern

Running bake in a terminal at the project root generates Tables, Entities, Controllers and Templates with sensible defaults for CRUD, validation and pagination. Generated code is starter code and still needs review for security, authorization and business rules before production use.

Trade‑off: fewer queries versus larger result sets

Using contain() together with paginate() reduces database round trips on index pages. The limitation is result set size and memory use. Deep or wide contains, such as Articles → Comments → Users → Profiles, can produce many rows and increase hydration cost.

Selective field lists and limiting association depth help. You can also contain only what the current page needs and defer heavy associations to a detail view.

A practical way to check the result is to enable query logging in a test app and compare query counts with and without contain on a paginated index. Inspect the Table class to confirm association definitions and foreign keys. Test pagination edge cases with empty results and with a larger contain graph to observe memory and performance behavior.

Version sensitivity matters. ORM API names and defaults differ between CakePHP 3, 4 and 5, so verify the method signatures in your version. Keep contains shallow for list pages, use them deliberately for detail pages, and review Bake output before shipping.