Pagination and Counting

Paginating a Doctrine query correctly, why a plain count over a join is wrong, and cursor pagination for large tables.

Syntax(new OffsetPaginator())->paginate($query, Window::fromPageNumberAndSize($page, 25))

Pagination needs two answers from one query: the rows for this page, and how many rows there are in total. Writing it yourself with setFirstResult() and setMaxResults() works until the query joins a collection, and then both answers go wrong.

Why a naive count is wrong

Join an article to its comments and the database returns one row per comment. COUNT(a.id) then counts comment rows, not articles, so the total is inflated and the last pages come back empty. Worse, LIMIT 25 cuts the result mid-article, so page one holds fewer than twenty-five articles with some of their comments missing.

The paginator handles this. With fetchJoinCollection: true it runs three queries: a DISTINCT count, a DISTINCT query for the ids on this page, and a WHERE IN query for the rows. Set it to false when the query joins no to-many collection and it uses two. If you count by hand instead, count distinct identifiers.

Offset and cursor

OffsetPaginator takes a Window — a position and a page size — and returns a page object with getItems(), getTotalCount() and getPageCount(). Window::fromPageNumberAndSize(1, 25) is the same value as new Window(0, 25); page numbers are one-based, offsets are zero-based.

Offsets get slower the deeper you go, because the database still walks the rows it skips. CursorPaginator avoids that: it remembers the ORDER BY values of the last row and asks for rows after them, so page five thousand costs what page one costs. The price is no jumping to an arbitrary page.

What trips people up: a cursor query needs a deterministic order.

Example

Example · php
<?php
// src/Controller/ArticleListController.php

declare(strict_types=1);

namespace App\Controller;

use App\Repository\ArticleRepository;
use Doctrine\ORM\Tools\Pagination\OffsetPaginator;
use Doctrine\ORM\Tools\Pagination\Window;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpKernel\Attribute\MapQueryParameter;
use Symfony\Component\Routing\Attribute\Route;

class ArticleListController extends AbstractController
{
    public function __construct(
        private readonly ArticleRepository $articles,
    ) {
    }

    #[Route('/articles', name: 'app_article_index', methods: ['GET'])]
    public function index(#[MapQueryParameter] int $page = 1): Response
    {
        // A repository method that returns the Query, unexecuted.
        $query = $this->articles->publishedWithCommentsQuery();

        // true: the query fetch-joins a to-many collection, so the
        // paginator counts and limits on distinct article ids.
        $result = (new OffsetPaginator(fetchJoinCollection: true))
            ->paginate($query, Window::fromPageNumberAndSize(max(1, $page), 25));

        return $this->render('article/index.html.twig', [
            'articles' => $result->getItems(),
            'total' => $result->getTotalCount(),
            'pages' => $result->getPageCount(),
        ]);
    }
}

When to use it

  • Page an admin list that joins each row to a collection, without the totals drifting.
  • Serve an infinite-scroll API over a table too large for OFFSET to stay fast.
  • Show a correct "42 results" heading above a filtered, joined listing.

More examples

The count that lies, and the count that does not

This is the bug behind nearly every broken pager: the total is larger than the number of items the list can ever show, so the final pages render empty. Either count distinct identifiers, or let the paginator produce the total, which is what it does internally.

Example · php
// src/Repository/ArticleRepository.php (methods on the generated class)

// WRONG on a to-many join: the join produces one row per comment,
// so this returns the number of comment rows.
public function countWithCommentsWrong(): int
{
    return (int) $this->getEntityManager()->createQuery(
        'SELECT COUNT(a.id)
         FROM App\Entity\Article a
         JOIN a.comments c
         WHERE a.status = :status'
    )->setParameter('status', 'published')->getSingleScalarResult();
}

// Right: count each article once.
public function countWithComments(): int
{
    return (int) $this->getEntityManager()->createQuery(
        'SELECT COUNT(DISTINCT a.id)
         FROM App\Entity\Article a
         JOIN a.comments c
         WHERE a.status = :status'
    )->setParameter('status', 'published')->getSingleScalarResult();
}

The legacy Paginator class

You will meet this form in existing projects and in older answers online. It solves the same problem with the same three queries, but the window lives on the Query instead of in a Window object. Recognise it so you can read the code; prefer OffsetPaginator in new work, where the page object carries the totals.

Example · php
// src/Controller/ArticleListController.php (inside an action)

use Doctrine\ORM\Tools\Pagination\Paginator;

$query = $this->entityManager
    ->createQuery(
        'SELECT a, c
         FROM App\Entity\Article a
         JOIN a.comments c
         ORDER BY a.id ASC'
    )
    // The older class reads the window off the query itself.
    ->setFirstResult(($page - 1) * 25)
    ->setMaxResults(25);

$paginator = new Paginator($query, fetchJoinCollection: true);

// Paginator is Countable, so count() runs the COUNT query.
$total = count($paginator);

// It is also IteratorAggregate, so it iterates the page's entities.
foreach ($paginator as $article) {
    // one Article per iteration, with its comments already loaded
}

Cursor pagination for a large table

The cursor is an encoded copy of the last row's ORDER BY values, so the database can seek straight to the next range instead of counting past the rows it skips. Use this for infinite scroll, feeds and API endpoints over millions of rows. It cannot offer a page-number control, and getTotalCount() on the page costs an extra COUNT query.

Example · php
// src/Controller/ArticleFeedController.php (inside an action)

use Doctrine\ORM\Tools\Pagination\CursorPaginator;

// Every ORDER BY item must be an entity field, and the set must be
// unique: publishedAt can repeat, a.id cannot.
$query = $this->entityManager
    ->createQuery(
        'SELECT a
         FROM App\Entity\Article a
         WHERE a.status = :status
         ORDER BY a.publishedAt DESC, a.id DESC'
    )
    ->setParameter('status', 'published');

// $cursor comes from ?cursor=... and is null or '' on the first request.
$page = (new CursorPaginator(limit: 25))->paginate($query, $cursor);

$payload = [
    'items' => $page->getItems(),
    'nextCursor' => $page->hasNextPage() ? $page->getNextCursorAsString() : null,
];

// GET /feed returns 200 with 25 items and a nextCursor string;
// GET /feed?cursor=<that string> returns the next 25.

Related

symfony querybuildersymfony dqlsymfony hydrationsymfony doctrine performancesymfony json api

Discussion

  • Be the first to comment on this lesson.