DQL query with table join and two joins

Question

I have 2 objects:

/** * @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 cannot find a way to create a DQL query that calls SQL as follows:

 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 does not contain groups that do not have a user.

Answer 1

This relationship is ManyToMany, not an event trying to join the relationship table, only the related object ... Then you were right using the RIGHT JOIN ... for the SQL query, but Doctrine automatically determines the connection type from the FROM clause.

In DQL, only certain relationships are controlled by relationships, so you don't need USE or ON clauses ...

How about this?

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