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屋！