"NOT EXISTS" Query with Many to Many Relation Doctrine Symfony3

Question

I would like to build a query that brings me all the games for a logged in user that he has not yet joined. For this I have built these 2 Entities. They are connected by many to many.

class Game
{
    public function __construct()
    {
        $this->users = new ArrayCollection();
    }

    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     *
     * @var Users[]
     *
     * @ORM\ManyToMany(
     *     targetEntity="Domain\Entity\User",
     *     inversedBy="user",
     *     fetch="EAGER"
     * )
     */
    private $users;
/**
 * @return array
 */
public function getUsers() : array
{
    return $this->users->getValues();
}

/**
 * @param User $users
 */
public function setUser($users)
{
    if(is_array($users)){
        /** @var User $user */
        foreach ($users as $user){
            $this->users->add($user);
        }
    } else {
        $this->users->add($users);
    }
}
}

And the User Entity

class User implements AdvancedUserInterface
{

    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

}

The Entities has more attributes but i think they are not important. Also I tried these Query, but it doesn't work.

/**
 * @param User $user
 * @return array
 */
public function fetchAllNonActivatedWhereYouNotJoin(User $user): array
{

    $qb = $this->createQueryBuilder('g');
    $qb->select('g')
        ->innerJoin('g.users', 'u')
        ->where('u.id != :user')
        ->andWhere('g.activate = 0')
        ->setParameter('user', $user->getId())
        ->getQuery()->getResult();


    return $qb->getQuery()->getResult();
}

Does anyone know a solution? Its Symfony 3 and Doctrine in PHP 7.1

Answer 1

One way to do it is left join the 2 entities starting from the game repository as you do, with a join condition to the logged in user and have a condition that users is empty:

$qb->leftJoin('g.users', 'u', Join::WITH, 'u.id = :user')
->andWhere('g.activate = 0')
->having('COUNT(u) = 0')
->groupby('g')
->setParameter('user', $user->getId())
->getQuery()->getResult();

This works because of doctrine hydration, which hydrates the users property on the limited joined query(in this case each game will either have the logged in user or not in the users collection).

There are also other ways to achieve this

Be careful with this if you are doing consecutive queries with the query builder, as the entity manager keeps references to the already hydrated relations. Reference of the issue