Stop N+1 Queries in CakePHP ORM with Contain

The problem: one query per row

\n

When you fetch a list of articles and then access each article's author inside a loop, CakePHP ORM may run a separate query for every author. This is the N+1 problem: one initial query plus N additional queries, one for each related record. It slows down page rendering and can exhaust database connections under load.

\n\n

Useful takeaway

\n

Call contain() on the query object before executing it, passing the association names you need. CakePHP loads the related records together with the primary records, so iterating over results does not trigger extra queries. Apply this in CakePHP 4.x and 5.x table finders, controller actions, or custom query methods.

\n\n

How contain works

\n

contain is an option on a CakePHP ORM Query object. It tells the ORM to eager load associated records. Depending on the association type—belongsTo, hasOne, hasMany, or belongsToMany—the ORM generates a SQL JOIN or issues a separate SELECT for the associated table. Related records are attached to each primary entity under the association name, so templates and API responses can access them without lazy loading.

\n\n

Worked example

\n

Run this code inside a CakePHP controller action or table method. No special system permissions are required beyond normal application access.

\n\n

use App\\Model\\Table\\ArticlesTable;\n\n// In a controller action\npublic function index()\n{\n    $query = $this->Articles->find()\n        ->contain([\n            'Authors',\n            'Comments' => function ($q) {\n                return $q\n                    ->select(['id', 'article_id', 'body', 'created'])\n                    ->order(['Comments.created' => 'DESC']);\n            },\n            'Tags'\n        ]);\n\n    $articles = $query->all();\n\n    $this->set('articles', $articles);\n}\n

\n\n

In a template, you can loop without triggering additional queries:

\n\n

<?php foreach ($articles as $article): ?>\n    <h3><= h($article->title) ></h3>\n    <p>By <= h($article->author->name) ></p>\n\n    <?php foreach ($article->comments as $comment): ?>\n        <p><= h($comment->body) ></p>\n    <?php endforeach; ?>\n<?php endforeach; ?>\n

\n\n

Verify the result

\n

Enable SQL logging in config/app.php with debug set to true, then load the action. Compare the query log with and without contain. Without it, you typically see one query for articles followed by one query per article for authors or comments. With contain, you should see the article query plus a small fixed number of associated queries, regardless of result count.

\n\n

You can also inspect the generated query object:

\n\n

$query = $this->Articles->find()->contain(['Authors']);\ndebug($query->sql());\n

\n\n

For an automated check, write a test that uses ConnectionManager to count executed queries and asserts the query count is lower when contain is used versus omitted. Do not assume the reduction is guaranteed for every association shape; verify against your schema.

\n\n

Limits and common mistakes

\n

\n
• Timing: contain must be called before execution. Calling it after toArray(), first(), or all() has no effect because the SQL has already been generated.
\n
• Filtering side effects: Combining contain with matching() or where() conditions on the same association can filter related entities and cause expected data to disappear from results.
\n
• Depth and size: Deeply nested contain arrays increase SQL size and memory usage. Limit nesting depth and use select() to fetch only required fields.
\n
• Association type: The ORM chooses JOINs or separate SELECTs based on the association type. Do not assume every contained association produces a JOIN.
\n

\n\n

Practical checklist

\n

\n
1. Identify loops in templates or API serializers that access associated data.
\n
2. Add contain() to the query before execution.
\n
3. Enable SQL logging and confirm the query count stays flat as result count grows.
\n
4. Remove unused associations from contain to reduce memory and SQL size.
\n

\n\n

This is a read-only query operation, so no rollback procedure is required.