ManyToMany Doctrine join must return one row

Question

Suppose you have a classical ManyToMany relationship in Doctrine. For instance, that one describe in the official documentation :

/** @Entity */
class User
{
    // ...

    /**
     * Many Users have Many Groups.
     * @ManyToMany(targetEntity="Group")
     * @JoinTable(name="users_groups",
     *      joinColumns={@JoinColumn(name="user_id", referencedColumnName="id")},
     *      inverseJoinColumns={@JoinColumn(name="group_id", referencedColumnName="id")}
     *      )
     */
    private $groups;

    // ...

    public function __construct() {
        $this->groups = new \Doctrine\Common\Collections\ArrayCollection();
    }
}

/** @Entity */
class Group
{
    /**
    * @ORM\Column(type="string")
    */
    private $type;
}

In order to simplify the example, there's only 2 possible group types : A or B. One user cannot belong to 2 groups that have the same type. So, each user can belong between 0 to 2 groups.

Let's suppose now that I want to return one row per user (no hydration with the getArrayResults() method, like this :

----------------------------------------------------------------------------
| user.id    | group_A.type | group_A.id     | group_B.type | group_B.id   |
----------------------------------------------------------------------------
| 1          | A            | 10             | B            | 20           |
----------------------------------------------------------------------------
| 2          | A            | 10             | B            | 21           |
----------------------------------------------------------------------------
| 3          | A            | 11             | NULL         | NULL         |
----------------------------------------------------------------------------

How to translate that result in DQL ? I'm able to get it with innerJoin() method is all the user have a group. But if it is not the case, a leftJoin() query return more than 2 rows.

Answer 1

The main problem of this kind of query is due to the non systematic link between the class User and Group. If each User are linked to each Group, a simple DQL query will work :

$qb->select('u.id, gA.type, gA.id, gB.type, gB.id')
    ->from('User', 'u')
    ->innerJoin('u.groups', 'gA', 'WITH', 'gA.type = "A"')
    ->innerJoin('u.groups', 'gB', 'WITH', 'gB.type = "B"')

But in the example given above, the result will be because User #3 has no link with group #B :

----------------------------------------------------------------------------
| user.id    | group_A.type | group_A.id     | group_B.type | group_B.id   |
----------------------------------------------------------------------------
| 1          | A            | 10             | B            | 20           |
----------------------------------------------------------------------------
| 2          | A            | 10             | B            | 21           |
----------------------------------------------------------------------------

By using a leftJoin clause, the result will be a biggest table with more than 3 records (5 in the example above).

So the solution is there's no where clause on the query is to use combine MAX and groupBy :

$qb->select('u.id, gA.type, max(gA.id), gB.type, max(gB.id)')
    ->from('User', 'u')
    ->innerJoin('u.groups', 'gA', 'WITH', 'gA.type = "A"')
    ->innerJoin('u.groups', 'gB', 'WITH', 'gB.type = "B"')
    ->groupBy('u.id')

This will give the expected result :

----------------------------------------------------------------------------
| user.id    | group_A.type | group_A.id     | group_B.type | group_B.id   |
----------------------------------------------------------------------------
| 1          | A            | 10             | B            | 20           |
----------------------------------------------------------------------------
| 2          | A            | 10             | B            | 21           |
----------------------------------------------------------------------------
| 3          | A            | 11             | NULL         | NULL         |
----------------------------------------------------------------------------

If a where clause is applied on the query, this can be avoid :

$qb->select('u.id, gA.type, gA.id, gB.type, gB.id')
    ->from('User', 'u')
    ->innerJoin('u.groups', 'gA', 'WITH', 'gA.type = "A"')
    ->innerJoin('u.groups', 'gB', 'WITH', 'gB.type = "B"')
    ->where('gA.type = 10 AND gB.type = 20)

Giving the following result :

----------------------------------------------------------------------------
| user.id    | group_A.type | group_A.id     | group_B.type | group_B.id   |
----------------------------------------------------------------------------
| 1          | A            | 10             | B            | 20           |
----------------------------------------------------------------------------