php DotIN 函数中的子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11924164/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Subquery in doctrine2 notIN Function
提问by Marcel Djaman
I'd like to select members who are not in specific service. I have 3 tables :
我想选择不属于特定服务的成员。我有 3 张桌子：
membreservicemembre_service(relation betweenmembreandservice)
membreservicemembre_service（membre和之间的关系service）
I'm using doctrine 2 and in SQL my query is :
我正在使用学说 2，在 SQL 中我的查询是：
SELECT m.* FROM membre m WHERE m.`id` NOT IN (
SELECT ms.membre_id FROM membre_service ms WHERE ms.service_id != 29
)
In Doctrine, I do :
在教义中，我这样做：
$qb = $this->_em->createQueryBuilder();
$qb2 = $qb;
$qb2->select('m.id')
->from('Custom\Entity\MembreService', 'ms')
->leftJoin('ms.membre', 'm')
->where('ms.id != ?1')
->setParameter(1, $service);
$qb = $this->_em->createQueryBuilder();
$qb->select('m')
->from('Custom\Entity\Membre', 'm')
->where($qb->expr()->notIn('m.id', $qb2->getDQL())
);
$query = $qb->getQuery();
//$query->useResultCache(true, 1200, __FUNCTION__);
return $query->getResult();
I got the following error :
我收到以下错误：
Semantical Error] line 0, col 123 near 'm WHERE ms.id': Error: 'm' is already defined.
语义错误] line 0, col 123 near 'm WHERE ms.id': Error: 'm' is already defined。
回答by WizardZ
The same alias cannot be defined 2 times in the same query
同一别名不能在同一个查询中定义 2 次
$qb = $this->_em->createQueryBuilder();
$qb2 = $qb;
$qb2->select('m.id')
->from('Custom\Entity\MembreService', 'ms')
->leftJoin('ms.membre', 'm')
->where('ms.id != ?1');
$qb = $this->_em->createQueryBuilder();
$qb->select('mm')
->from('Custom\Entity\Membre', 'mm')
->where($qb->expr()->notIn('mm.id', $qb2->getDQL())
);
$qb->setParameter(1, $service);
$query = $qb->getQuery();
return $query->getResult();
Ideally you should use many-to-many relation for your entity, in this case your query is going to be much simpler.
理想情况下，您应该为实体使用多对多关系，在这种情况下，您的查询会简单得多。
回答by Comstar
Actually if you are using the Symfony2 repository class you could also do the following:
实际上，如果您使用的是 Symfony2 存储库类，您还可以执行以下操作：
$in = $this->getEntityManager()->getRepository('Custom:MembreService')
->createQueryBuilder('ms')
->select('identity(ms.m)')
->where(ms.id != ?1);
$q = $this->createQueryBuilder('m')
->where($q->expr()->notIn('m.id', $in->getDQL()))
->setParameter(1, $service);
return $q->getQuery()->execute();
回答by Stephan Vierkant
You can use (NOT) MEMBER OF:
您可以使用(NOT) MEMBER OF：
<?php
$query = $em->createQuery('SELECT m.id FROM Custom\Entity\Membre WHERE :service NOT MEMBER OF m.services');
$query->setParameter('service', $service);
$ids = $query->getResult();
See the documentationfor more examples.
有关更多示例，请参阅文档。
