时间戳

首页 > 解决方案 > 如何在学说查询生成器中使用 orWhere inside andWhere

symfony - 如何在学说查询生成器中使用 orWhere inside andWhere

问题描述

我想创建一个搜索，您可以在其中选择几个城市和这样的选项。

SELECT * FROM `company` WHERE (`city_id` = 4 OR `city_id` = 5) AND (`option` = 1 OR `option` = 2)

我怎样才能在教义中做到这一点？

/**
 * @param CompanyAdminSearch $search
 * @return Query
 */
public function findVisibleQuerySupplierSearch(CompanyAdminSearch $search): Query
{
    $query = $this->findVisibleQuerySupplier();

    if ($search->getCity()->count() > 0){
        $k = 0;
        foreach ($search->getCity() as $k => $city){
            $k++;
            $query = $query
                ->andWhere($query->expr()->orX("company.city = :city$k"))
                ->setParameter("city$k", $city);
        }
    }

    if ($search->getOptions()->count() > 0) {
        $k = 0;
        foreach ($search->getOptions() as $k => $option){
            $k++;
            $query = $query
                ->andWhere(":option$k MEMBER OF company.options")
                ->setParameter("option$k", $option);
        }
    }

    return $query->getQuery();
}

private function findVisibleQuerySupplier(): QueryBuilder
{
    return $this->createQueryBuilder('company')
        ->andWhere('company.metier = :metier')
        ->setParameter('metier', true)
        ;
}

我尝试使用 orX，但它不起作用。

有人能帮我吗？

标签： symfonydoctrine-ormdoctrinesymfony4

解决方案

WHERE (`city_id` = 4 OR `city_id` = 5) AND ...可以重写为WHERE `city_id` IN (4, 5) AND ...这将大大简化您的查询构建器并提供相同的结果。

您现在可以使用：

if ($search->getCity()->count() > 0) {
    $citiesId = [];
    foreach ($search->getCity() as $city) {
        $citiesId[] = $city; // get the cities in an array
    }

    // no need to write $query = $query->...
    $query->andWhere("company.city IN (:cities)") // use IN clause
          ->setParameter(":cities", $citiesId);   // and bind the array as parameter
}

您现在可以对option其他东西应用相同的逻辑

我不知道是什么$search->getCity()样子，但我觉得你可以使用

if ($search->getCity()->count() > 0) {
    $query->andWhere("company.city IN (:cities)")
          ->setParameter(":cities", $search->getCity());
}

推荐阅读