NOT IN query with Doctrine QueryBuilder in a Many to Many relation

Question

In my Symfony2 project, I have two entities "contact" and "settings", with a many to many relationship :

/**
 * @ORM\ManyToMany(targetEntity="AppBundle\Entity\Settings", cascade={"persist"})
 * @ORM\JoinColumn(nullable=true)
 */
private $settings;

Entity settings have a property "parametre", which is a simple string.

Now I want to get all the contacts that DON'T have any settings which "parametre" is "THEMES".

I can do that in SQL with a query like :

SELECT DISTINCT(c.id) FROM contact c WHERE c.id
NOT IN (SELECT cs.contact_id FROM contact_settings cs
INNER JOIN Settings s ON s.id = cs.settings_id
WHERE s.parametre = "THEMES")

But I can't figure out how to do it with Doctrine's query builder. Here's what I've tried so far :

$query = $this->createQueryBuilder('c')
    ->join('c.settings', 's');

$qb2 = $qb;

$qb2->select('s2')
    ->from('AppBundle\Entity\Settings', 's')
    ->where('s2.parametre = :parametre');

$query->where(($qb->expr()->notIn('s', $qb2->getDQL())));
$query->setParameter('parametre', 'THEMES');
$result = $query->getQuery()->getResult();

That doesn't return any result.

Thanks!

Answer 1

You can try something like this:

$subQueryBuilder = $this->getEntityManager()->createQueryBuilder();
$subQuery = $subQueryBuilder
    ->select(['cs.id'])
    ->from('AcmeDemoBundle:Contact', 'cs')
    ->innerJoin('cs.settings', 's')
    ->where('s.parameter = :parameter')
    ->setParameter('parameter', 'THEMES')
    ->getQuery()
    ->getArrayResult()
;

$queryBuilder = $this->getEntityManager()->createQueryBuilder();
$query = $queryBuilder
    ->select(['c'])
    ->from('AcmeDemoBundle:Contact', 'c')
    ->where($queryBuilder->expr()->notIn('c.id', ':subQuery'))
    ->setParameter('subQuery', $subQuery)
    ->getQuery()
;

$result = $query->getResult();

This is just an example to your question. I can't provide complete example as I don't know the structure of your entities...

Answer 2

The accepted answer from xurshid29 will use 2 queries. But by using the getDQL() inside the "->notIn()" function you can do it in 1 query:

$subQueryBuilder = $this->getEntityManager()->createQueryBuilder();
$subQuery = $subQueryBuilder
    ->select('cs.id')
    ->from('AcmeDemoBundle:Contact', 'cs')
    ->innerJoin('cs.settings', 's')
    ->where('s.parameter = :parameter') // move setParameter() to main queryBuilder!
;

$queryBuilder = $this->getEntityManager()->createQueryBuilder();
$query = $queryBuilder
    ->select('c')
    ->from('AcmeDemoBundle:Contact', 'c')
    ->where($queryBuilder->expr()->notIn('c.id', $subQuery->getDQL()))
    ->setParameter('parameter', 'THEMES') // setParameter() from the subQuery must go here
    ->getQuery()
;

$result = $query->getResult();

Warning! Do not try to set the DQL as a parameter, it will result in a string instead of real SQL syntax. So this is wrong and will not work:

// THIS IS WRONG
->where($queryBuilder->expr()->notIn('c.id', ':subQuery'))
->setParameter('subQuery', $subQuery->getDQL())