在doctrine2 notIN函数中的子查询 [英] Subquery in doctrine2 notIN Function
本文介绍了在doctrine2 notIN函数中的子查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想选择不具体服务的会员。我有3个表:
I'd like to select members who are not in specific service. I have 3 tables :
-
membre
-
服务
-
membre_service
(服务
)
membre
service
membre_service
(relation betweenmembre
andservice
)
使用doctrine 2和SQL我的查询是:
I'm using doctrine 2 and in SQL my query is :
SELECT m.* FROM membre m WHERE m.`id` NOT IN (
SELECT ms.membre_id FROM membre_service ms WHERE ms.service_id != 29
)
在Doctrine中,我做:
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();
我收到以下错误:
语义错误]行0,col 123 near'm WHERE ms.id':错误:'m'已经定义。
Semantical Error] line 0, col 123 near 'm WHERE ms.id': Error: 'm' is already defined.
推荐答案
相同的别名不能在同一查询中定义2次
The same alias cannot be defined 2 times in the same query
$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.
这篇关于在doctrine2 notIN函数中的子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文