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
– jere, Commented Feb 9, 2012 at 16:11
For building pagination with doctrine2 have a look at this extension: github.com/beberlei/DoctrineExtensions — Stefan
– Stefan, Commented Feb 10, 2012 at 10:13
@Stefan it is now part of ORM. docs.doctrine-project.org/en/latest/tutorials/pagination.html — Eugene
– Eugene, Commented Jan 16, 2014 at 16:33

Cerad · Accepted Answer · 2019-08-13 11:27:24Z

562

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, 2019 at 11:27

answered Feb 9, 2012 at 17:38

Cerad

49k9 gold badges92 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jovan Perovic Over a year ago

He didn't ask for a count without predicates (bar = $bar) ;)

Cerad Over a year ago

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.

jere Over a year ago

+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

webbiedave Over a year ago

The most portable way is to do $qb->select($qb->expr()->count('account.id'))

Stepan Yudin Over a year ago

can anybody explain why i must use select('count(account.id)') instead of select('count(account)') ?

|

Flexo - Save the data dump · Accepted Answer · 2014-11-27 11:07:14Z

70

Here is another way to format the query:

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

edited Nov 27, 2014 at 11:07

Flexo - Save the data dump♦

89.1k22 gold badges205 silver badges286 bronze badges

answered Nov 22, 2014 at 19:55

HappyCoder

6,1937 gold badges50 silver badges78 bronze badges

3 Comments

barbieswimcrew Over a year ago

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.

Maxim Mandrik Over a year ago

You can write this

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

Darius.V Over a year ago

Just notice - do not use addSelect() for this, even in xdebug watch, this makes it not work.

luchaninov · Accepted Answer · 2018-09-28 16:21:45Z

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

Nathan Kot · Accepted Answer · 2014-08-21 23:00:25Z

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, 2014 at 23:00

Nathan Kot

2,4321 gold badge23 silver badges31 bronze badges

3 Comments

Paolo Stefan Over a year ago

Useful, but please note: this solution will work for queries on a single entity- with complex select statements, it will just refuse to work.

Vladyslav Kolesov Over a year ago

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

cwhisperer Over a year ago

$paginator->getTotalItemCount() would be a solution too

4b0 · Accepted Answer · 2018-05-08 09:53:59Z

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 8, 2018 at 9:53

4b0

22.4k30 gold badges97 silver badges143 bronze badges

answered May 8, 2018 at 9:48

Sławomir Kania

6417 silver badges9 bronze badges

1 Comment

Wilt Over a year ago

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: github.com/doctrine/orm/issues/6290

Sergey Poskachey · Accepted Answer · 2015-06-29 19:50:07Z

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.
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!

Starx · Accepted Answer · 2015-12-02 10:49:06Z

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 · Accepted Answer · 2016-07-27 12:31:34Z

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?

crmpicco · Accepted Answer · 2017-02-14 07:20:28Z

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

Strahil Ruychev · Accepted Answer · 2023-06-07 14:58:16Z

In Symfony 6 this is probably the easiest way:

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

Abhi Das · Accepted Answer · 2020-02-19 13:23:22Z

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

Collectives™ on Stack Overflow

Count Rows in Doctrine QueryBuilder

11 Answers 11

6 Comments

3 Comments

Comments

3 Comments

1 Comment

2 Comments

Comments

Comments

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

6 Comments

3 Comments

Comments

3 Comments

1 Comment

2 Comments

Comments

Comments

Comments

Comments

1 Comment

Linked

Related