How to do multiple counts in one DQL query using Doctrine@ ORM?

Question

I am trying to do multiple counts in one DQL query using Doctrine2 ORM.

Here is what my query looks like:

$em = $this->getDoctrine()->getManager();
$query = $em->createQuery('

SELECT      COUNT(u.id) AS users,
            COUNT(t.id) AS teams,
            COUNT(p.id) AS posts,
            COUNT(c.id) AS comments
    FROM    ProjectUserBundle:User u,
            ProjectTeamBundle:Team t,
            ProjectPostBundle:Post p,
            ProjectCommentBundle:Comment c,
            ');
$counts = $query->getSingleResult();

It returns me the following result: {"users":"2648940","teams":"2648940","posts":"2648940","comments":"2648940"}

It must be something like: {"users":"17","teams":"5","posts":"190","comments":"321"}

Someone can tell me where I do it wrong?

Answer 1

With your query, DQL mixes the tables instead of counting rows in each table. The result is an erroneous number of rows. It can't explain why and how, but it always fail when I use more than one COUNT() function in a query.

To count the right number of rows, you have to use sub-queries:

$em = $this->getDoctrine()->getManager();
$query = $em->createQuery('

SELECT      COUNT(U.id) AS users,
            (SELECT COUNT(T) FROM ProjectUserBundle:Team T) AS teams,
            (SELECT COUNT(P) FROM ProjectUserBundle:Post P) AS posts,
            (SELECT COUNT(C) FROM ProjectUserBundle:Comment C) AS comments
    FROM    ProjectUserBundle:User U
            ');

$counts = $query->getSingleResult();