NOT IN with Doctrine QueryBuilder in a many-to-many ratio
In my Symfony2 project, I have two objects, "contact" and "settings" with many relationships:
/**
* @ORM\ManyToMany(targetEntity="AppBundle\Entity\Settings", cascade={"persist"})
* @ORM\JoinColumn(nullable=true)
*/
private $settings;
Entity settings have a "parameter" property, which is a simple string.
Now I want to get all contacts that do NOT have any settings, which are "THEMES" as "parameter".
I can do it 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 using the Doctrine 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();
This does not return any result.
Thank!
source to share
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 of your question. I cannot provide a complete example as I do not know the structure of your entities ...
source to share
The accepted answer from xurshid29 will use 2 requests. But using getDQL () inside the "-> notIn ()" function, you can do it in 1 request:
$subQueryBuilder = $this->getEntityManager()->createQueryBuilder();
$subQuery = $subQueryBuilder
->select('cs.id')
->from('AcmeDemoBundle:Contact', 'cs')
->innerJoin('cs.settings', 's')
->where('s.parameter = :parameter')
->setParameter('parameter', 'THEMES')
;
$queryBuilder = $this->getEntityManager()->createQueryBuilder();
$query = $queryBuilder
->select('c')
->from('AcmeDemoBundle:Contact', 'c')
->where($queryBuilder->expr()->notIn('c.id', $subQuery->getDQL()))
->getQuery()
;
$result = $query->getResult();
Warning! Don't try to set DQL as a parameter, it will result in the string appearing instead of the actual SQL syntax. So it's wrong and won't work
// THIS IS WRONG
->where($queryBuilder->expr()->notIn('c.id', ':subQuery'))
->setParameter('subQuery', $subQuery->getDQL())
source to share