Symfony2 & Doctrine - Get number of rows returned from datasource

Symfony2 & Doctrine - Get number of rows returned from datasource

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Counting Rows in Doctrine Queries: The COUNT Dilemma

You are working with a Symfony2 repository and have constructed a query using Doctrine's QueryBuilder to filter entities based on specific criteria. Your current setup looks like this:

$query = $this->createQueryBuilder('foo')
    ->where('foo.bar = :id')
    ->setParameter('id', $myID)
    ->getQuery();

The immediate question is how to determine the number of rows returned by the database without actually executing a SELECT statement that loads every entity into memory. This is a common scenario when you need to implement pagination, check for existence before processing, or simply log statistics. The answer depends entirely on how much data you are willing to fetch and what performance constraints your application faces.

Using execute() with count()

The most straightforward approach in Doctrine is to execute the query as a standard object hydration query and then use PHP's built-in count() function. This method is simple to implement:

$entities = $query->getResult();
$count = count($entities);

However, this approach has a significant performance penalty if your dataset is large. When you call getResult(), Doctrine executes the SQL query against the database and materializes every matching row into PHP objects. If you are querying a table with thousands of rows, you are forcing the application to instantiate thousands of entities, populate their properties, and potentially trigger lazy-loaded relationships before you simply discard them to get a count. This wastes both CPU cycles and memory.

The COUNT Query Approach

If you only need the row count, Doctrine offers a way to generate a SQL query that returns a single scalar value representing the total number of rows matching your criteria. You can achieve this by calling setMaxResults(1) or, more accurately for this specific use case, utilizing the underlying database's aggregation capabilities directly through a custom DQL or by switching to count().

In Doctrine ORM 2.x (Symfony2 era), there isn't a direct getQuery()->count() method on the object itself that executes immediately. Instead, you typically need to construct a new query specifically for counting or use the database's native count function. A common pattern involves creating a sub-query or using the DQL aggregate function:

$countQuery = $this->createQueryBuilder('foo')
    ->select('COUNT(foo.id) as total')
    ->where('foo.bar = :id')
    ->setParameter('id', $myID);

$result = $countQuery->getQuery()->getArrayResult();
$count = reset($result)['total'];

This approach generates a SQL query like SELECT COUNT(*) FROM foo WHERE bar = ?. This is far more efficient because the database engine calculates the count internally without returning the full row data. The result set contains only one column, minimizing network traffic and memory usage on the application server.

Trade-offs and Best Practices

Choosing between fetching results and counting them requires understanding your specific needs. If you intend to iterate over the results immediately after checking the size, using getResult() first might be acceptable for small datasets. However, if the count is used solely for conditional logic (e.g., "if there are more than 10 items, paginate"), always use a dedicated count query.

There is also a consideration regarding database locking and transaction isolation levels. In some high-concurrency environments, reading the raw row count via SQL can be less intrusive than fetching rows, as it avoids holding locks on individual record data structures for longer periods.

While Symfony2 applications often integrate with various third-party services, tools like those found at https://laravelcompany.com sometimes discuss similar architectural patterns in the broader PHP ecosystem. While Laravel handles this differently with its Eloquent ORM (often using $model->count() directly), understanding the underlying mechanism of how Doctrine generates these SQL statements helps you write better, more portable code across frameworks.

By explicitly selecting COUNT rather than fetching entities, you ensure your application scales efficiently as data volumes grow.

Our network