DQL many to many and count

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):

/**
 * EntityE_User
 *
 * @ORMTable(name="users")
 * @ORMEntity
 */
class E_User
{
    /**
     * @ORMManyToMany(targetEntity="E_Group", cascade={"persist"})
     * @ORMJoinTable(name="users_groups",
     *      joinColumns={@ORMJoinColumn(name="user_id", referencedColumnName="id", onDelete="cascade")},
     *      inverseJoinColumns={@ORMJoinColumn(name="group_id", referencedColumnName="id", onDelete="cascade")}
     * )
     */

    protected $groups;

    /**
     * @var integer $id
     *
     * @ORMColumn(name="id", type="integer")
     * @ORMId
     * @ORMGeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string $name
     *
     * @ORMColumn(name="name", type="string", length=255)
     */
    private $name;

    /* ... other attributes & getters and setters ...*/
}


/**
 * EntityE_Group
 *
 * @ORMTable(name="groups")
 * @ORMEntity
 */
class E_Group
{
    /**
     * @var integer $id
     *
     * @ORMColumn(name="id", type="integer")
     * @ORMId
     * @ORMGeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string $name
     *
     * @ORMColumn(name="text", type="string", length=255)
     */
    private $name;

    /* ... other attributes & getters and setters ...*/
}

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 EntityGroup 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 EntityUser u " .
    "JOIN u.groups g GROUP BY g.id ORDER BY cnt DESC LIMIT 10;";