Symfony2 - Doctrine2 QueryBuilder WHERE IN ManyToMany 字段 [英] Symfony2 - Doctrine2 QueryBuilder WHERE IN ManyToMany field
问题描述
:)
预先感谢您帮助我解决此问题:
Thank you in advance for helping me with this issue:
我有一个实体 Hotel,它与实体 HotelService 存在多对多关系
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
和 GROUPBY
子句
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();
}
<小时>
在上面的查询中,我又添加了一个选择来计算每家酒店的不同服务 ID,即
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(例如 ids (1,2,3))的酒店,因此当我们在其执行或操作中使用时,应返回包含这 3 个服务的酒店where servid_id = 1 or servid_id = 2 servid_id = 3
这正是你不想要 AND
操作,酒店必须有这 3 个所以我通过有一部分转换了这个逻辑
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
->have('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 WHERE IN ManyToMany 字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!