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.

Moisture

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

Dewclaw

It give me back only that groups, which has users. But I need empty groups as well, with count 0 – Moisture

Humm... I updated by inverting the FROM and JOIN table, it should be better now... – Dewclaw