DQL many to many and count

Question

I'm using Symfony 2 with Doctrine, and I've got two entities joined in a many to many association. Let's say I have two entities: User and Group, and the related tables on db are users, groups and users_groups. I'd like to get the top 10 most populated groups in DQL, but I don't know the syntax to perform queries on the join table (users_groups). I already looked on the Doctrine manual but I didn't found the solution, I guess I still have a lot to learn about DQL. In plain sql that would be: ``` select distinct group_id, count(*) as cnt from users_groups group by group_id order by cnt desc limit 10 ``` Can you please help me to translate this to DQL? Update (classes): ``` /** * Entity\E_User * * @ORM\Table(name="users") * @ORM\Entity */ class E_User { /** * @ORM\ManyToMany(targetEntity="E_Group", cascade={"persist"}) * @ORM\JoinTable(name="users_groups", * joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id", onDelete="cascade")}, * inverseJoinColumns={@ORM\JoinColum…

Accepted Answer

It's not easy without seeing the actual classes, but by guessing you have a many-to-many bidirectional relationship:

```
$dql = "SELECT g.id, count(u.id) as cnt FROM Entity\Group g " .
    "JOIN g.users u GROUP BY g.id ORDER BY cnt DESC LIMIT 10;";
$query = $em->createQuery($dql);
$popularGroups = $query->getArrayResult();
```

UPDATE:

You don't have to use a bidirectional relationship, you can query the other way around:

```
$dql = "SELECT g.id, count(u.id) as cnt FROM Entity\User u " .
    "JOIN u.groups g GROUP BY g.id ORDER BY cnt DESC LIMIT 10;";
```

DQL many to many and count

Solution

Problem

Related problems