与多义关系中的Doctrine QueryBuilder不在查询中 [英] NOT IN query with Doctrine QueryBuilder in a Many to Many relation
问题描述
在我的Symfony2项目中,我有两个实体联系人"和设置",并且具有多对多关系:
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;
实体设置具有属性"parametre",它是一个简单的字符串.
Entity settings have a property "parametre", which is a simple string.
现在,我想获取所有没有任何设置的联系人,其中"parametre"是"THEMES".
Now I want to get all the contacts that DON'T have any settings which "parametre" is "THEMES".
我可以在SQL中通过类似这样的查询来做到这一点:
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")
但是我不知道如何使用Doctrine的查询构建器来做到这一点. 到目前为止,这是我尝试过的:
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();
这不会返回任何结果.
谢谢!
推荐答案
您可以尝试执行以下操作:
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...
这篇关于与多义关系中的Doctrine QueryBuilder不在查询中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!