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

doctrine, dql, php, symfony

Solution

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

Problem

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?

Original source