DQL query with joining table and two join

Question

I have 2 entity:

/**
 * @ORM\Entity
 * @ORM\Table(name="users")
 */
class User
{
    /**
     * @ORM\ManyToMany(targetEntity="Myapp\UserBundle\Entity\Group")
     * @ORM\JoinTable(name="user_groups",
     *      joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="group_id", referencedColumnName="id")}
     * )
     */
    protected $groups;

    ...
}

and

/**
 * @ORM\Entity(repositoryClass="Myapp\UserBundle\Repository\GroupRepository")
 * @ORM\Table(name="groups")
 */
class Group
  ...

I cant find a way to create a DQL query which results SQL like this:

SELECT g.name, g.id, count( u.id )
FROM users u
LEFT JOIN user_groups ug ON u.id = ug.user_id
RIGHT JOIN groups g ON g.id = ug.group_id
GROUP BY g.id

I tried and failed whith:

$this->getEntityManager()
    ->createQuery('
        SELECT g.id, g.name, count(u.id) as usercount FROM MyappUserBundle:User u
        JOIN u.groups g
        GROUP BY g.id'
    );

since the result not contains the groups that has no user.

Answer 1

It's a ManyToMany relation, don't event try to join on the relation table, only the related entity... Then, you were right with the RIGHT JOIN ... for a SQL query, but Doctrine automatically defines the jointure type from the FROM clause.

In DQL, only defined relations are managed by jointures, so you don't need USE or ON clauses...

What about this one ?

SELECT g.name, g.id, count( u.id )
FROM groups g
JOIN users u
GROUP BY g.id