Ми}{@лbI4

I could not help noticing how many questions about it, because even I had tried to found an answer. The problem is we have not a good answer on this issue at all.

For instance, you have a difficult query, and you need to fetch COUNT(*) by it, in the internet you could find follow 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 is fine for not big tables, because of slow speed. But what do we need to do if we need to run a heavy query there are a lots of data? Very simple! AST Walkers is our solution. Unfortunately, not everyone reads a documentation, where this solution has been written, and search doesn't always has the fit solution.

On the page, how to create custom AST Walkers, already has an example using to count rows. I'm going to show you a more extended 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 be like SELECT COUNT(*) FROM (our sub query).

That's all. =) Also, I recommend to you see Doctrine\ORM\Tools\Pagination\Paginator as I extracted "more extended version" from there. ;)