Doctrine Pagination: When to Use Paginator Versus Plain LIMIT/OFFSET

Why Plain LIMIT/OFFSET Breaks With Fetch Joins

You are building an invoice listing page. Each invoice can have many line items, and you want to eager-load them with a fetch join so the template does not trigger lazy-load queries. A fetch join loads the related rows in the same query instead of issuing separate queries later. You write a Doctrine QueryBuilder, call setFirstResult() and setMaxResults(), and expect ten invoices per page. Instead, page two shows the same invoice twice, or skips an invoice entirely.

The problem is SQL, not Doctrine. When you join Invoice to InvoiceLine, the database produces one row per line item. Doctrine applies LIMIT and OFFSET to those joined rows. If an invoice has three lines, it occupies three rows in the result set. A page boundary can land in the middle of those rows, returning duplicate invoices on one page and omitting the next invoice on the following page.

When the Paginator Wrapper Is Required

Wrap the query in Doctrine\ORM\Tools\Pagination\Paginator when the DQL uses a fetch join on a collection. Doctrine then runs a separate COUNT query for the total and an identifier subquery that pages root entity IDs before loading the full entities. Because the LIMIT applies to the ID subquery, each page returns distinct invoices with their line items attached.

For a flat query without fetch joins—such as listing invoices alone—plain setFirstResult() and setMaxResults() is sufficient and avoids the extra queries.

Worked Example: Invoices With Line Items

Assume these entities exist in your project:

/** @Entity */
class Invoice
{
    /** @Id @Column(type="integer") @GeneratedValue */
    private $id;

    /** @OneToMany(targetEntity="InvoiceLine", mappedBy="invoice") */
    private $lines;
}

/** @Entity */
class InvoiceLine
{
    /** @Id @Column(type="integer") @GeneratedValue */
    private $id;

    /** @ManyToOne(targetEntity="Invoice", inversedBy="lines") */
    private $invoice;
}

The following service method paginates invoices with their lines:

use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Tools\Pagination\Paginator;

function listInvoices(EntityManagerInterface $em, int $page, int $pageSize): array
{
    $qb = $em->createQueryBuilder()
        ->select('i', 'l')
        ->from('Invoice', 'i')
        ->leftJoin('i.lines', 'l')
        ->orderBy('i.createdAt', 'DESC')
        ->addOrderBy('i.id', 'ASC');

    $qb->setFirstResult(($page - 1) * $pageSize)
       ->setMaxResults($pageSize);

    $paginator = new Paginator($qb, $fetchJoinCollection = true);

    $invoices = iterator_to_array($paginator, false);
    $total = count($paginator);

    return ['invoices' => $invoices, 'total' => $total];
}

The second constructor argument tells Doctrine to expect a fetch join collection. Omitting it can cause Doctrine to hydrate only scalar identifiers instead of full entities.

Diagnostic: Inspect the Generated SQL

Run this in a development script or test bootstrap, not in production traffic:

use Doctrine\DBAL\Logging\DebugStack;

$logger = new DebugStack();
$em->getConnection()->getConfiguration()->setSQLLogger($logger);

// execute your paginated query here

foreach ($logger->queries as $executed) {
    var_export($executed['sql']);
    echo "\n";
}

Look for three statements: a COUNT against the root table, a SELECT ... WHERE id IN (...) identifier subquery, and the main entity query. If you see only one SELECT ... LIMIT ... OFFSET with a join, you are using plain pagination and may receive duplicates.

Required permissions: the database user needs SELECT on the invoices and invoice_lines tables. No schema changes are required. Risk: the DebugStack logger retains every query in memory, so disable it after debugging to avoid memory growth on long-running requests.

Trade-offs and Limitations

• Extra queries: Paginator adds a COUNT query and an identifier subquery. On small tables the cost is negligible; on large tables it can dominate page load time.
• Indexing: The COUNT and subquery use the same WHERE and ORDER BY columns as the main query. Add indexes on createdAt and any filtered columns, or the COUNT can become a full table scan.
• Version sensitivity: Doctrine ORM 2.x and 3.x generate different subquery syntax. Review the CHANGELOG after upgrading and rerun pagination tests.
• Not for partial objects: Do not combine Paginator with SELECT i.id or native SQL. It expects full entity DQL.

Practical Verification

1. Persist two invoices, each with three line items.
2. Request page one with a page size of one.
3. Assert that the result contains exactly one invoice and that its line collection has three items.
4. Request page two and assert it contains the second invoice, not a duplicate of the first.

If the assertions pass and the SQL log shows the COUNT and identifier subquery, pagination is behaving correctly. If you see duplicate root entities, remove the fetch join or switch to Paginator.

Actionable Takeaway

Use Doctrine’s Paginator whenever your query eager-loads a collection with a fetch join. Use plain setFirstResult() and setMaxResults() for simple, flat queries. Check your composer.lock for the doctrine/orm version, inspect the generated SQL in development, and verify distinct root entities with a page size of one before deploying.