SELECT COUNT(*) FROM (SUB_QUERY) by using Doctrine 2

I noticed how many questions there were about this because I had tried to find an answer myself. The problem is that there doesn't seem to be a good answer.

For example, suppose you have a complex query and need to get its COUNT(*). Searching online, you might find the following solution:

$subQuery = $qb->select('main.id')
    ->from('Order\Entity\Order', 'main')
    ->where('main.published = :published')
    ->setParameters([
        'published' => true,
    ]);

$qb->select('COUNT(o)')
    ->from('Order\Entity\Order', 'o')
    ->where($qb->expr()->in('o.id', $subQuery->getDQL()))
    ->setParameters($subQuery->getParameters());

return $qb->getQuery()->getResult();

This works for small tables, but it can be slow. What should you do if you need to run a complex query on a large dataset? The answer is simple: use AST walkers. Unfortunately, not everyone reads the documentation where this solution is described, and search engines don't always find the right answer.

The page on creating custom AST walkers already includes an example of how to count rows. Here's a more complete version:

$platform = $entityManager->getConnection()->getDatabasePlatform();

$rsm = new \Doctrine\ORM\Query\ResultSetMapping();
$rsm->addScalarResult($platform->getSQLResultCasing('dctrn_count'), 'count');

$query = clone $mainQuery;

$query->setHint(\Doctrine\ORM\Query::HINT_CUSTOM_OUTPUT_WALKER, 'Doctrine\ORM\Tools\Pagination\CountOutputWalker');
$query->setResultSetMapping($rsm);
$query->setParameters($mainQuery->getParameters());

return $query->getQuery()->getResult();

After that, our query will look like SELECT COUNT(*) FROM (our subquery).

That's all. =) I also recommend looking at Doctrine\ORM\Tools\Pagination\Paginator, where I found the basis for this more complete version. ;)