Better to use DQL for getting Column Count or Get Collection Then Count?

Question

I am quite sure that DQL will be the way to go, but I am wondering if Doctrine, i am using Doctrine 2, has someway to return the row count. I won't be using the rows itself, I just want the count.

Hymenium

Answer 1

I'm new to Doctrine2 but it looks like you can simply do this:

$query = $em->createQuery('SELECT COUNT(u.id) FROM Entities\User u');
$count = $query->getSingleScalarResult();

Source (Using Agregate Functions): http://www.doctrine-project.org/docs/orm/2.0/en/reference/dql-doctrine-query-language.html#dql-select-examples

Allowed aggregate functions: http://www.doctrine-project.org/docs/orm/2.0/en/reference/dql-doctrine-query-language.html#aggregate-functions

Frig

The syntax that worked for me was $em->createQuery('SELECT COUNT(u.id) FROM MyBundle:User u');. By the way, in Rails, this same thing can be done with User.count. In Django, User.objects.count. – Mayhap

@JasonSwett you've used aliases defined for you by the Symfony framework. It won't work outside of Symfony, unless you define aliases yourself – Frig

Answer 2

Here is another interesting point of view about using aggregated functions in DQL

http://doctrine-orm.readthedocs.org/en/latest/cookbook/aggregate-fields.html

Maybe you would avoid the creation of an specific query to obtain an aggregate value. In this case, aggregate fields are a good alternative.

Grebe