Count Rows in Doctrine QueryBuilder

Question

Something like:

$qb = $entityManager->createQueryBuilder();
$qb->select('count(account.id)');
$qb->from('ZaysoCoreBundle:Account','account');

$count = $qb->getQuery()->getSingleScalarResult();

Some folks feel that expressions are somehow better than just using straight DQL. One even went so far as to edit a four year old answer. I rolled his edit back. Go figure.

Here is another way to format the query:

return $repository->createQueryBuilder('u')
            ->select('count(u.id)')
            ->getQuery()
            ->getSingleScalarResult();

It's better to move all logic of working with database to repositores.

So in controller you write

/* you can also inject "FooRepository $repository" using autowire */
$repository = $this->getDoctrine()->getRepository(Foo::class);
$count = $repository->count();

And in Repository/FooRepository.php

public function count()
{
    $qb = $repository->createQueryBuilder('t');
    return $qb
        ->select('count(t.id)')
        ->getQuery()
        ->getSingleScalarResult();
}

It's better to move $qb = ... to separate row in case you want to make complex expressions like

public function count()
{
    $qb = $repository->createQueryBuilder('t');
    return $qb
        ->select('count(t.id)')
        ->where($qb->expr()->isNotNull('t.fieldName'))
        ->andWhere($qb->expr()->orX(
            $qb->expr()->in('t.fieldName2', 0),
            $qb->expr()->isNull('t.fieldName2')
        ))
        ->getQuery()
        ->getSingleScalarResult();
}

Also think about caching your query result - http://symfony.com/doc/current/reference/configuration/doctrine.html#caching-drivers

public function count()
{
    $qb = $repository->createQueryBuilder('t');
    return $qb
        ->select('count(t.id)')
        ->getQuery()
        ->useQueryCache(true)
        ->useResultCache(true, 3600)
        ->getSingleScalarResult();
}

In some simple cases using EXTRA_LAZY entity relations is good
http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/tutorials/extra-lazy-associations.html

If you need to count a more complex query, with groupBy, having etc... You can borrow from Doctrine\ORM\Tools\Pagination\Paginator:

$paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);
$totalRows = count($paginator);

Since Doctrine 2.6 it is possible to use count() method directly from EntityRepository. For details see the link.

https://github.com/doctrine/doctrine2/blob/77e3e5c96c1beec7b28443c5b59145eeadbc0baf/lib/Doctrine/ORM/EntityRepository.php#L161