How to avoid a lot of queries when counting associated entities with Doctrine?

Question

Imagine an User entity with an association with Group (many-to-many). I need to display user details (along with - say - group names or groups count). In my controller (Symfony 2) I can get all users:

$users = $this->em->getRepository('My\Entity\User')
    ->getAll();

// Assign $users to whatever the view engine
return $this->render('users.html.twig', array('users' => $users));

Of course , being the association LAZY by default, no JOIN operations with Group are performed. This is good most of the time.

But what happens when I try to get group count in my view?

{% for user in users %}
    {{ user.groups|length }}
{% endfor %}

The result is: one query performed for each raw. That is, the number of the query equals the number of the user. What happens with 1000 users (and no pagination)?

How can fetch all associated entity for the User class (i.e. issue just one query)?

Answer 1

You do that by creating custom repository method where you join your entities. More info -> http://symfony.com/doc/master/book/doctrine.html#joining-related-records