Count Rows in Doctrine QueryBuilder

Question

I'm using Doctrine's QueryBuilder to build a query, and I want to get the total count of results from the query.

$repository = $em->getRepository('FooBundle:Foo');

$qb = $repository->createQueryBuilder('n')
        ->where('n.bar = :bar')
        ->setParameter('bar', $bar);

$query = $qb->getQuery();

//this doesn't work
$totalrows = $query->getResult()->count();

I just want to run a count on this query to get the total rows, but not return the actual results. (After this count query, I'm going to further modify the query with maxResults for pagination.)

you just want to return the number of results? your code is not very clear. why doesn't getQuery() work? — jere, Feb 09 '12 at 16:11
For building pagination with doctrine2 have a look at this extension: https://github.com/beberlei/DoctrineExtensions — Stefan, Feb 10 '12 at 10:13
@Stefan it is now part of ORM. http://docs.doctrine-project.org/en/latest/tutorials/pagination.html — Eugene, Jan 16 '14 at 16:33

Cerad · Accepted Answer · 2019-08-13T11:27:24.870

551

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.

edited Aug 13 '19 at 11:27

answered Feb 09 '12 at 17:38

Cerad

48,157
8
90
92

1

He didn't ask for a count without predicates (`bar = $bar`) ;) – Jovan Perovic Feb 10 '12 at 01:06
5

He accepted your answer so I guess all is well. I was under the impression that he only wanted a count without the overhead of actually retrieving the rows which my example shows. There is of course no reason why where conditions could not be added. – Cerad Feb 10 '12 at 14:19
55

+1 for using getSingleScalarResult(). using `count()` on `$query->getResult()` is actually making the query return the results (which is what he *didn't* want). i think this should be accepted answer – jere Feb 10 '12 at 14:30
27

The most portable way is to do `$qb->select($qb->expr()->count('account.id'))` – webbiedave May 23 '16 at 22:04
2

can anybody explain why i must use `select('count(account.id)')` instead of `select('count(account)')` ? – Stepan Yudin May 27 '16 at 09:09
@Sliq - One reason is that the entity manager has no from method so it would not work. Working code (no matter how ugly) is generally considered superior to non-working code. You could in fact use $em->findAll('entity')->count() but that involves some additional overhead as all the entity records would be loaded. By the way, Symdony does not require Doctrine so feel free to use your own database access library. – Cerad Oct 27 '16 at 16:43

score 65 · Answer 2 · edited Nov 27 '14 at 11:07

65

Here is another way to format the query:

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

edited Nov 27 '14 at 11:07

Flexo

87,323
22
191
272

answered Nov 22 '14 at 19:55

HappyCoder

5,985
6
42
73

Using the fluent interface is a different approach that is very helpful in case you intend to write static queries. If there is the need to toggle where conditions for example executing each method on it's own has it's advantages as well. – barbieswimcrew Mar 14 '16 at 22:07
3

You can write this `return ($qb = $repository->createQueryBuilder('u'))->select($qb->expr()->count('u.id'))->getQuery()->getSingleScalarResult();` – Maxim Mandrik Dec 01 '18 at 11:08

luchaninov · Answer 3 · 2018-09-28T16:21:45.180

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

score 22 · Answer 4 · answered Aug 21 '14 at 23:00

22

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);

answered Aug 21 '14 at 23:00

Nathan Kot

2,392
1
21
31

8

Useful, but please note: this solution will work for queries on a single entity- with complex select statements, it will just refuse to work. – Paolo Stefan Sep 05 '14 at 07:06
this solution produces additional query like `SELECT COUNT(*) AS dctrn_count FROM (_ORIGINAL_SQL_) dctrn_result) dctrn_table` which is actually nothing special but well known COUNT(*) solution – Vladyslav Kolesov Feb 23 '16 at 17:32
$paginator->getTotalItemCount() would be a solution too – cwhisperer Mar 29 '17 at 07:07

score 17 · Answer 5 · edited May 08 '18 at 09:53

17

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

edited May 08 '18 at 09:53

4b0

21,981
30
95
142

answered May 08 '18 at 09:48

Sławomir Kania

621
7
8

Yes, it looks like a great solution and works for more simple cases (you can pass criteria for filtering the counting), but I didn't manage to get it to work for criteria with associations (filtering by associations). See related post here: https://github.com/doctrine/orm/issues/6290 – Wilt Feb 08 '19 at 16:33

Sergey Poskachey · Answer 6 · 2015-06-29T19:50:07.307

Example working with grouping, union and stuff.

Problem:

 $qb = $em->createQueryBuilder()
     ->select('m.id', 'rm.id')
     ->from('Model', 'm')
     ->join('m.relatedModels', 'rm')
     ->groupBy('m.id');

For this to work possible solution is to use custom hydrator and this weird thing called 'CUSTOM OUTPUT WALKER HINT':

class CountHydrator extends AbstractHydrator
{
    const NAME = 'count_hydrator';
    const FIELD = 'count';

    /**
     * {@inheritDoc}
     */
    protected function hydrateAllData()
    {
        return (int)$this->_stmt->fetchColumn(0);
    }
}
class CountSqlWalker extends SqlWalker
{
    /**
     * {@inheritDoc}
     */
    public function walkSelectStatement(AST\SelectStatement $AST)
    {
        return sprintf("SELECT COUNT(*) AS %s FROM (%s) AS t", CountHydrator::FIELD, parent::walkSelectStatement($AST));
    }
}

$doctrineConfig->addCustomHydrationMode(CountHydrator::NAME, CountHydrator::class);
// $qb from example above
$countQuery = clone $qb->getQuery();
// Doctrine bug ? Doesn't make a deep copy... (as of "doctrine/orm": "2.4.6")
$countQuery->setParameters($this->getQuery()->getParameters());
// set custom 'hint' stuff
$countQuery->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, CountSqlWalker::class);

$count = $countQuery->getResult(CountHydrator::NAME);

I'd rather just write a native query than to deal with that Rube Goldberg code. — keyboardSmasher, Oct 16 '15 at 07:39
That's a good example of how shitty Symfony is: Something simple like a basic everyday SQL-count needs to be solved with totally complicated self-written stuff... wow, i mean, just wow! Still thanks to Sergey for this answer! — Sliq, Jul 03 '19 at 10:45

score 5 · Answer 7 · answered Dec 02 '15 at 10:49

For people who are using only Doctrine DBAL and not the Doctrine ORM, they will not be able to access the getQuery() method because it doesn't exists. They need to do something like the following.

$qb = new QueryBuilder($conn);
$count = $qb->select("count(id)")->from($tableName)->execute()->fetchColumn(0);

Oleksii Zymovets · Answer 8 · 2016-07-27T12:31:34.303

To count items after some number of items (offset), $qb->setFirstResults() cannot be applied in this case, as it works not as a condition of query, but as an offset of query result for a range of items selected (i. e. setFirstResult cannot be used togather with COUNT at all). So to count items, which are left I simply did the following:

   //in repository class:
   $count = $qb->select('count(p.id)')
      ->from('Products', 'p')
      ->getQuery()
      ->getSingleScalarResult();

    return $count;

    //in controller class:
    $count = $this->em->getRepository('RepositoryBundle')->...

    return $count-$offset;

Anybody knows more clean way to do it?

score 0 · Answer 9 · answered Feb 14 '17 at 07:20

Adding the following method to your repository should allow you to call $repo->getCourseCount() from your Controller.

/**
 * @return array
 */
public function getCourseCount()
{
    $qb = $this->getEntityManager()->createQueryBuilder();

    $qb
        ->select('count(course.id)')
        ->from('CRMPicco\Component\Course\Model\Course', 'course')
    ;

    $query = $qb->getQuery();

    return $query->getSingleScalarResult();
}

score 0 · Answer 10 · answered Feb 19 '20 at 13:23

0

You can also get the number of data by using the count function.

$query = $this->dm->createQueryBuilder('AppBundle:Items')
                    ->field('isDeleted')->equals(false)
                    ->getQuery()->count();

answered Feb 19 '20 at 13:23

Abhi Das

500
8
11

2

its not working, count is not a function – Maxime Krier May 30 '22 at 13:39

score 0 · Answer 11 · answered Jun 07 '23 at 14:58

0

In Symfony 6 this is probably the easiest way:

$this->manager()->getRepository(SomeEntity::class)->count(['field' => $value])

answered Jun 07 '23 at 14:58

Strahil Ruychev

1
1

Count Rows in Doctrine QueryBuilder

11 Answers11

Linked