Symfony2-Doctrine2 QueryBuilder在ManyToMany字段中 [英] Symfony2 - Doctrine2 QueryBuilder WHERE IN ManyToMany field
问题描述
:)
预先感谢您为我解决此问题:
Thank you in advance for helping me with this issue:
我有一家实体酒店,它与实体HotelService有一个ManyToMany关系
I've an entity Hotel which has a ManyToMany relation with an entity HotelService
我如何构建(如果可能,使用QueryBuilder)一个查询以选择所有具有作为数组参数给出的服务子集的酒店?
how can i build (with QueryBuilder if possible) one query to select all hotels having a subset of services given as array parameter?
例如:H1(S1,S2,S3,S4),H2(S2,S3,S4),H3(S1,S2,S3)
Example: H1(S1, S2, S3, S4), H2(S2, S3, S4), H3(S1, S2, S3)
查询子集(S1,S2)必须返回H1和H3.
Querying with subset (S1, S2) has to return H1 and H3.
我尝试了很多事情,这是一些代码摘录:
I have tried many things, this is some code extract:
public function findByServices($services) {
$qb = $this->createQueryBuilder('hotel')
->addSelect('location')
->addSelect('country')
->addSelect('billing')
->addSelect('services')
->innerJoin('hotel.billing', 'billing')
->innerJoin('hotel.location', 'location')
->innerJoin('location.city', 'city')
->innerJoin('location.country', 'country');
->innerJoin('hotel.services', 'services');
$i = 0;
$arrayIds = array();
foreach ($services as $service) {
$arrayIds[$i++] = $service->getId();
}
$qb->add('where', $qb->expr()->in('services', $arrayIds))->getQuery();
}
此代码返回$ arrayIds中有一个服务ID的所有酒店.
This code return all hotels for which there is ONE service id in $arrayIds.
我要相反(服务包含$ arrayIds中所有ID的酒店).
I want the opposite (hotels for which services contain ALL ids in $arrayIds).
当然,反转expr()-> in中的参数并不能解决问题,并且会产生错误的参数错误.
Of course, inversing parameters in expr()->in doesn't solve the problem, and create bad parameters errors.
有人可以帮我吗? (对不起,我的英语不好):)
Can somebody help me please? (sorry for my bad english) :)
推荐答案
对于您的解决方案,您可以将COUNT(DISTINCT)
与HAVING
和GROUP BY
子句一起使用
For your solution you can make use of COUNT(DISTINCT)
with HAVING
and GROUP BY
clauses
public function findByServices($services)
{
$qb = $this->createQueryBuilder('hotel')
->addSelect('location')
->addSelect('country')
->addSelect('billing')
->addSelect('services')
->addSelect('COUNT(DISTINCT services.id) AS total_services')
->innerJoin('hotel.billing', 'billing')
->innerJoin('hotel.location', 'location')
->innerJoin('location.city', 'city')
->innerJoin('location.country', 'country')
->innerJoin('hotel.services', 'services');
$i = 0;
$arrayIds = array();
foreach ($services as $service) {
$arrayIds[$i++] = $service->getId();
}
$qb->add('where', $qb->expr()->in('services', $arrayIds))
->addGroupBy('hotel.id')
->having('total_services = '.count($arrayIds))
->getQuery();
}
在上面的查询中,我又添加了一个选择,为每个酒店(即
In above query i have added one more select as counting distinct service ids for each hotel i.e
-> addSelect('COUNT(DISTINCT services.id)AS HIDDEN total_services')
->addSelect('COUNT(DISTINCT services.id) AS HIDDEN total_services')
然后我还需要一个分组依据,因此我添加了
Then i also need a group by for that count so i added
-> addGroupBy('hotel.id')
->addGroupBy('hotel.id')
现在这是一个棘手的部分,因为您提到您需要具有所有服务ID的酒店,例如ID(1,2,3),因此当我们在执行或操作类似的服务时,应返回包含这3个服务的酒店where servid_id = 1 or servid_id = 2 servid_id = 3
正是您不希望酒店必须具有这3个AND
操作,所以我通过包含
Now here comes the tricky part as you mentioned that you need hotel that have all the service ids like ids (1,2,3) so hotels that contains these 3 service should be returned when we use in its performs or operation like where servid_id = 1 or servid_id = 2 servid_id = 3
which is exactly you don't want the AND
operation that hotel must have these 3 so i converted this logic by having part
->具有('total_services ='.count($ arrayIds))
->having('total_services = '.count($arrayIds))
现在total_services
是查询的虚拟别名,并保存每个酒店的不同计数,因此我已将此计数与IN()
部分中提供的ID的计数进行了比较,这将返回必须包含这些服务的酒店
Now total_services
is a virtual alias for the query and holds the distinct count for each hotel so i have compared this count to the count of provided ids in IN()
part this will return the hotels which must contains these services
这篇关于Symfony2-Doctrine2 QueryBuilder在ManyToMany字段中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!