"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

@maiorano84 it works, but it returns all games. But it shuld return only the games the user dosent join — Cadien, Feb 20 '18 at 21:50
1.If you want the games the user has not join, the query seems to be ok. _"I would like to build a query that brings me all the games for a logged in user that he has not yet joined"_ 2.If the user is not joined it should not have any game. please check your bussiness logic, your post reads that you want to bring **all the games**. 3.The query works for both logic — Juan I. Morales Pestana, Feb 21 '18 at 13:15

score 0 · Accepted Answer · answered Feb 21 '18 at 19:38

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

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

1 Answers1