Your CakePHP Index Page Is Running 101 Queries. contain() Fixes That.

You ship a blog-style index page in CakePHP. It lists 25 articles, each showing its author's name. Locally it feels fine. Then you open the DebugKit toolbar on a staging copy with real data and see 26 SQL statements for one page: one for the articles, then one per article to fetch its author. That's the N+1 problem, and CakePHP's ORM makes it easy to trigger because associations are loaded lazily when you touch them in a view.

The fix is one method: contain(). It tells the ORM which associations to eager load, so the page runs a small, fixed number of queries no matter how many rows you render.

Why lazy loading bites list pages

When you call $this->Articles->find(), CakePHP fetches articles only. The first time a template reads $article->author->name, the ORM quietly fires a query for that author. Do that inside a loop over 25 articles and you get 25 extra round trips. Each one is fast, but latency adds up, and under load the database does far more work than the page actually needs.

The awkward part is that nothing looks wrong in your code. The view is clean, the controller is clean, and the queries are invisible unless you're watching the query log. That's why this usually gets discovered in production rather than in development.

contain() in practice

The idiomatic controller pattern for an index action looks like this (CakePHP 4/5 conventions; check the book for your installed major version):

// In src/Controller/ArticlesController.php
public function index()
{
    $query = $this->Articles->find()
        ->contain(['Authors', 'Tags']);

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

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

contain() composes cleanly with paginate(), so eager loading survives pagination. Behind the scenes the ORM uses different strategies per association type: belongsTo and hasOne are typically joined into the main query, while hasMany and belongsToMany are fetched in separate queries and stitched onto the parent entities. That stitching matters — a naive join across a hasMany would multiply rows and inflate the result set.

You can go deeper with nested paths and closures:

$query = $this->Articles->find()
    ->contain([
        'Authors.Profiles',
        'Tags' => function ($q) {
            return $q->select(['id', 'name', 'article_id']);
        },
    ]);

The closure form lets you trim columns or add conditions to the association query. One important caveat: a condition inside contain() filters the related records, not the parent rows. If you need "only articles that have a published tag", you want matching() or innerJoinWith() instead — a common mix-up that produces confusing results.

Prove it worked

Don't assume eager loading happened because you typed contain(). If you accidentally call it on a different query object than the one you execute, nothing changes. The reliable check is the query count:

1. Enable the DebugKit toolbar in development, or temporarily enable the query log on the connection.
2. Load the index page and count SQL statements before the change: expect roughly 1 + n for n articles.
3. Add contain(), reload, and count again: expect a small constant — typically 2–4 statements depending on association types.

For a more durable guard, write an integration test asserting that a known fixture set hydrates the expected related entities, so a future refactor that drops the contain() fails loudly.

The trade-off: don't contain the world

Eager loading isn't free. Containing a wide association with large text columns, or nesting three levels deep, can be slower than the handful of extra queries you were trying to avoid — you're pulling and hydrating data the view never renders. The right rule is simple: contain exactly what the template touches, nothing more. When an index page and an API endpoint need different related data, give each its own finder method (e.g. findForIndex()) rather than one over-eager query shared by both.

Closing

If you take one action from this: open your busiest list page with DebugKit on and count the queries. If the number scales with the row count, add contain() for the associations the view renders, then count again. It's a five-minute change that often removes an order of magnitude of database chatter — just verify the drop in the query log rather than trusting the code.