CakePHP Pagination: Use Contain to Stop Hidden N+1 Queries

The problem: your blog index is issuing one query per author

Your CakePHP blog index feels fast in development, then slows down in production as the posts table grows. The slowdown is often not the main query that fetches posts, but a loop of extra queries that load each post’s author, tags, or comments one row at a time. At the same time, pagination links can show the wrong total page count because the count query does not match the item query.

The practical fix is to declare eager-loaded associations explicitly in your pagination configuration instead of relying on CakePHP’s defaults.

This applies to CakePHP 4.x and 5.x. Both versions support the contain option in pagination settings. CakePHP 5’s Paginator service adds stricter type hints, but the containment strategy behaves the same way.

Set contain in the pagination config

In src/Controller/PostsController.php, add the associations you actually render in the view to the paginate property. This tells the ORM to fetch them up front rather than issuing a separate query for every post.

public function index()
{
    $this->paginate = [
        'limit' => 25,
        'contain' => ['Authors', 'Tags'],
        'order' => ['Posts.created' => 'DESC'],
    ];
    $posts = $this->paginate($this->Posts);
    $this->set(compact('posts'));
}

contain is CakePHP’s eager-loading directive. It loads related records in the same query or a small set of associated queries, instead of one query per row as you iterate through $posts in the template. For a blog index that lists each post’s author, omitting Authors from contain can turn 25 posts into 26 or more database round trips.

Confirm the query count with Debug Kit

Load the blog index page in your browser with Debug Kit enabled. Open the SQL panel and look for two things:

1. One SELECT for posts with LIMIT and OFFSET matching the requested page.
2. One SELECT COUNT(*) for the total number of matching posts.

You should not see a separate SELECT for each author or tag. If you do, contain is missing or misspelled.

Permissions: no special server permissions are required to view the toolbar; you only need access to the running application. Risk: do not enable Debug Kit in production, because it exposes query details and application internals.

Manual pagination requires a manual page count

If you bypass $this->paginate() and call page() and limit() directly on a query object, CakePHP does not calculate pageCount for you. This pattern suits custom pagination beyond the helper’s defaults, but you must compute the total yourself.

$query = $this->Posts->find()
    ->where(['Posts.status' => 'published'])
    ->contain(['Authors']);
$posts = $query->limit(25)->page(2)->all();
$pageCount = (int) ceil($query->count() / 25);

To verify alignment, run the same conditions through paginate() and through the manual count() approach; both should produce the same pageCount. Run the check against a dataset larger than one page so the LIMIT and OFFSET behavior is meaningful.

Trade-offs: eager loading is not free

• Memory and result size. Adding too many associations or deeply nested contain paths can make the single query slower and increase memory use.
• Selected fields. If you restrict the query to specific columns and omit the primary key, pagination controls in the view may break. Always include id when selecting specific fields.
• Version upgrades. Moving from CakePHP 4 to 5 can reset pagination defaults. Re-check your paginate configuration after an upgrade so page links and fields do not disappear.

What to change before your next deploy

Open your blog index controller, add the rendered associations to contain, then load the page with Debug Kit and confirm that only two SQL queries run. If you use custom find() pagination, replace any assumed page count with an explicit count() calculation. That one configuration change removes the hidden per-row queries and keeps your page totals accurate as the blog grows.