Doctrine: Counting an entity's items with a condition

Question

How can I count an entity's items with a condition in Doctrine? For example, I realize that I can use:

$usersCount = $dm->getRepository('User')->count();

But that will only count all users. I would like to count only those that have type employee. I could do something like:

$users = $dm->getRepository('User')->findBy(array('type' => 'employee'));
$users = count($users);

That works but it's not optimal. Is there something like the following:?

$usersCount = $dm->getRepository('User')->count()->where('type', 'employee');

That is really ridiculous that basic Repository class can not count entities satisfying simple conditions. This feature definitely should appear in Doctrine wish list. — zelibobla, May 15 '15 at 11:03
Why not `$users = $dm->getRepository('User')->findBy(array('type' => 'employee'))->count();` ? — Boris K, Jan 18 '17 at 16:05
@BorisK : it will populate all your User then count. Not the best way to do it. — Bacteries, Feb 25 '17 at 18:02

score 28 · Accepted Answer · edited Jul 26 '20 at 12:14

28

Well, you could use the QueryBuilder to setup a COUNT query:

Presuming that $dm is your entity manager.

$qb = $dm->createQueryBuilder();

$qb->select($qb->expr()->count('u'))
   ->from('User', 'u')
   ->where('u.type = ?1')
   ->setParameter(1, 'employee');

$query = $qb->getQuery();

$usersCount = $query->getSingleScalarResult();

Or you could just write it in DQL:

$query = $dm->createQuery("SELECT COUNT(u) FROM User u WHERE u.type = ?1");
$query->setParameter(1, 'employee');

$usersCount = $query->getSingleScalarResult();

The counts might need to be on the id field, rather than the object, can't recall. If so just change the COUNT(u) or ->count('u') to COUNT(u.id) or ->count('u.id') or whatever your primary key field is called.

edited Jul 26 '20 at 12:14

gpalex

826
1
11
27

answered Sep 30 '13 at 21:41

Orbling

20,413
3
53
64

1

I was actually trying to avoid this, but I guess there is no other way to do it. Thank you. – luqita Oct 04 '13 at 15:11
1

@luqita: Avoid what exactly - a query? This will perform an aggregate query on your database server, which is the most efficient way to get that data generally. Are you more concerned with avoiding typing? You could extend the entity manager to provide a convenience method of the type you specified. – Orbling Oct 04 '13 at 15:20

score 16 · Answer 2 · edited Jul 31 '18 at 07:41

16

This question is 3 years old but there is a way to keep the simplicity of the findBy() for count with criteria.

On your repository you can add this method :

    public function countBy(array $criteria)
    {
        $persister = $this->_em->getUnitOfWork()->getEntityPersister($this->_entityName);
        return $persister->count($criteria);
    }

So your code will looks like this :

$criteria = ['type' => 'employee'];
$users = $repository->findBy($criteria, ['name' => 'ASC'], 0, 20);
$nbUsers = $repository->countBy($criteria);

edited Jul 31 '18 at 07:41

D4V1D

5,805
3
30
65

answered Feb 25 '17 at 18:00

Bacteries

593
4
13

1

That would be fine, if it worked. On Symfony 2.7 it raises an exception... " Attempted to call an undefined method named "count" of class "Doctrine\ORM\Persisters\BasicEntityPersister"" – Steve Childs Jul 09 '17 at 22:49
3

In fact, the count() method on the EntityPersister is available since Doctrine ORM 2.3 (so it's only available since Symfony 2.8). Upgrade your symfony to 2.8 will give you a LTS version and this method ;) – Bacteries Jul 11 '17 at 08:51
Ah, that would explain it. As to migrating to 2.8x, yes, I'd love to - sadly there's a heck of a lot of refactoring required in our system! It'll happen eventually though, 2.7 has only 12 months support left. – Steve Childs Jul 12 '17 at 14:17
great answer! Thank you – jonyjm Nov 01 '17 at 23:27
I was confused by this at first and then realized it should be $repository->countBy($criteria); to match the method name. Then it works. Thanks! – Allen Jan 05 '18 at 19:25
awesome! this should have been built-in – Michail Michailidis Oct 05 '18 at 12:52

score 12 · Answer 3 · answered Jul 27 '20 at 14:44

12

Since doctrine 2.6 you can just count the result of the provided conditions when you don't really need the data:

$usersCount = $dm->getRepository('User')->count(array('type' => 'employee'));

Upgrade to 2.6

answered Jul 27 '20 at 14:44

Serhii Vasko

383
4
11

score 0 · Answer 4 · answered Jan 06 '23 at 17:19

Just got on an old project and also needed to get the count for a QueryBuilder when the Where conditions are already set.

I added the following function to my service to get the result :

public function getTotalRecordsFromQueryBuilder(QueryBuilder $queryBuilder): int
{
    $alias = $queryBuilder->getRootAliases()[0];
    $clone = clone $queryBuilder;
    $clone->select(sprintf('COUNT(%s)', $alias));
    return $clone->getQuery()->getSingleScalarResult();
}

I clone the QueryBuilder to not change it for the futur additions to it.

Then I call that function after adding my Where criteria

$queryBuilder = $manager->createQueryBuilder()
            ->select('import')
            ->from('App:Import', 'import');

$dataTableService->addWhereDQLFromRequest($request, $queryBuilder, $sortColumns);
$dataTableService->addOrderDQLFromRequest($request, $queryBuilder, $sortColumns);
$totalRecords = $dataTableService->getTotalRecordsFromQueryBuilder($queryBuilder);
$dataTableService->addLimitDQLFromRequest($request, $queryBuilder);

Doctrine: Counting an entity's items with a condition

4 Answers4

Linked