Efficient Pagination in Doctrine ORM: Using the Paginator to Avoid Memory Overload

Problem: Loading entire collections wastes memory

When you retrieve a collection with Doctrine’s default hydration, the ORM issues a single SELECT that returns every matching row. For tables with thousands of records, this means hydrating thousands of objects into PHP memory, which can cause slow page loads or even memory‑exhaustion errors on list views.

Thesis: Doctrine’s Paginator limits rows and preserves joins

The Doctrine\ORM\Tools\Pagination\Paginator wraps a QueryBuilder and adds two queries per page: a distinct count query to determine total results, and a limited query with LIMIT and OFFSET that returns only the entities needed for the current page. This keeps memory usage low while still allowing fetch‑joins for associated data.

Understanding the default behavior

Without pagination, a repository method might look like this:

$posts = $entityManager->getRepository(BlogPost::class)
    ->findBy([], ['createdAt' => 'DESC']);

Doctrine executes:

SELECT p0_.id AS id_0, p0_.title AS title_1, p0_.content AS content_2, p0_.createdAt AS createdAt_3
FROM blog_post p0_
ORDER BY p0_.createdAt DESC

All rows are loaded, then hydrated into BlogPost objects.

Setting up the Paginator

First, build a QueryBuilder that includes any fetch‑joins you need. Then pass it to the Paginator, tell Doctrine to use the walker that adds the distinct query, and finally set the limit and offset based on the page number and page size.

use Doctrine\ORM\Tools\Pagination\Paginator;
use Doctrine\ORM\Query\QueryWalker;

$qb = $entityManager->createQueryBuilder()
    ->select('p', 'c')
    ->from(BlogPost::class, 'p')
    ->leftJoin('p.comments', 'c')          // fetch‑join to avoid N+1 later
    ->orderBy('p.createdAt', 'DESC');

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

$page = max(1, (int)$_GET['page'] ?? 1);   // obtain from request, sanitize
$limit = 10;
$offset = ($page - 1) * $limit;

$paginator->getQuery()
    ->setFirstResult($offset)
    ->setMaxResults($limit);

// Iterate over the paginator to get the entities for this page
$posts = [];
foreach ($paginator as $post) {
    $posts[] = $post;
}

The Paginator will execute two SQL statements per request:

1. A distinct count query (e.g., SELECT COUNT(DISTINCT p0_.id) FROM blog_post p0_ LEFT JOIN comment c1_ ON p0_.id = c1_.post_id).
2. The limited data query with LIMIT ? OFFSET ? and the same joins.

Worked example: BlogPost with comments in a Twig template

Assuming the above code lives in a Symfony controller action, you can pass $posts and pagination metadata to Twig:

return $this->render('blog/index.html.twig', [
    'posts' => $posts,
    'page' => $page,
    'totalPages' => (int)ceil($paginator->count() / $limit),
]);

The Twig template then renders each post and its already‑fetched comments:

{% for post in posts %}
    
        
{{ post.title }}
        
{{ post.content|truncate(150) }}
        
            Posted on {{ post.createdAt|date('M j, Y') }}
            

                {% for comment in post.comments %}
                    
{{ comment.author }}: {{ comment.text }}
                {% endfor %}
            
        
    
{% endfor %}


    {% if page > 1 %}
        ← Previous
    {% endif %}
    Page {{ page }} of {{ totalPages }}
    {% if page < totalPages %}
        Next →
    {% endfor %}

Trade‑off: Extra distinct query vs. memory savings

The distinct count query can become expensive when the SELECT includes many joins, aggregates, or complex expressions because the database must compute distinct values over a larger intermediate result. In such cases you might consider:

• Using a simple scalar count query (e.g., counting primary keys only) if you can guarantee uniqueness.
• Enabling Doctrine’s second‑level cache or query cache to reduce the cost of the repeated count.
• Fetch‑joining only the associations you actually need to keep the result set small.

Regardless, the memory savings are usually significant: instead of hydrating thousands of objects, you hydrate only the page size (e.g., 10 objects).

Limitations and practical verification

The built‑in Paginator works well for entity selections with joins, but it does not automatically handle:

• Scalar selects that use DQL functions like COUNT, GROUP BY, or HAVING without a distinct walker.
• Queries that already contain a DISTINCT clause; you may need to adjust the walker or provide a custom count query.

To verify that pagination is behaving as expected:

1. Enable Doctrine’s SQL logger (in Symfony, configure doctrine.dbal.logging: true in the dev environment).
2. Load a page and check the profiler: you should see exactly two queries per request—the distinct count and the limited data query.
3. Run a memory‑profile tool such as Xdebug or Blackfire before and after adding the Paginator; observe a drop in peak memory usage when navigating large lists.
4. Write a simple PHPUnit test that asserts the paginator’s count() matches the expected total and that the generated SQL contains LIMIT and OFFSET placeholders.

Actionable closing

If your application displays lists that could grow beyond a few hundred rows, replace plain findBy or custom QueryBuilder iteration with the Doctrine Paginator. Start by adding fetch‑joins for any associations you accessed inside the loop to avoid N+1 queries, then measure memory and query count as described. The trade‑off is an extra SELECT, but for most web‑scale workloads the memory and latency gains outweigh that cost.