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?

A.L · Accepted Answer · 2013-11-26 10:27:46Z

4

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

answered Nov 26, 2013 at 10:27

A.L

10.6k10 gold badges73 silver badges106 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related