douwo4837
2014-10-22 14:24
浏览 220
已采纳

Doctrine QueryBuilder查询:具有多个关联

I'm building a search form for secondhand cars. In the form the user can select checkboxes for the options the car should have. Because the user can select multiple options, i need to search a car that has all options the user has selected.

I'm trying to build a query that checks if the 'car' has the selected options. Right now im able to check if the car has one of the selected options by doing :

// The options filter is something special, the parameter is passed as a comma separated
// String with options ids
        if($param == 'car.options' && $optionsIds){
            $queryBuilder->leftJoin('car.options', 'option');
            $options        = $queryBuilder->expr()->orX();
            foreach(explode(',', $optionsIds) as $id) {
                $options->add($queryBuilder->expr()->eq('option.id', $id));
            }
            $wheres->add($options);
            continue;
        } 

But when i change the 'orX' with an 'andX' the result is always returned empty.

The codesnippet above is part of a larger function.

图片转代码服务由CSDN问答提供 功能建议

我正在构建二手车的搜索表单。 在表单中,用户可以选择汽车应具有的选项的复选框。 因为用户可以选择多个选项,我需要搜索具有用户选择的所有选项的汽车。

我正在尝试构建一个检查“汽车”是否具有的查询 选定的选项。 现在我可以通过以下方式检查汽车是否具有所选选项之一:

  //选项过滤器是特殊的,参数以逗号分隔的方式传递\  n //带有选项ids的字符串
 if($ param =='car.options'&& $ optionsIds){
 $ queryBuilder-> leftJoin('car.options','option'); 
  $ options = $ queryBuilder-> expr() - > orX(); 
 foreach(explode(',',$ optionsIds)as $ id){
 $ options-> add($ queryBuilder->  expr() - > eq('option.id',$ id)); 
} 
 $ wheres-> add($ options); 
 continue; 
} 
   
 
 

但是当我用'andX'更改'orX'时,结果总是返回空。

上面的codesnippet是更大函数的一部分。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dongshaoxiong0012 2014-10-23 09:42
    已采纳

    I finally managed to get it working. The trick was was using a subquery :

    $carsWithSelectedOptions = $this->getEntityManager()->createQuery('
        SELECT c.id
        FROM Occasions\Model\Bo\Car AS c JOIN c.options o
        WHERE o.id IN ('.$optionsIds.')
        AND c.id = car.id
        GROUP BY c.id
        HAVING COUNT(o.id) >= '.count(explode(',', $optionsIds))
    );
    
    $wheres->add($queryBuilder->expr()->exists($carsWithSelectedOptions->getDQL()));
    

    Note this is part of a larger query. The full query results in :

    SELECT advert, customer, car, country, brand, model 
    FROM Occasions\Model\Bo\Advert advert 
    LEFT JOIN advert.customer customer 
    LEFT JOIN advert.car car 
    LEFT JOIN customer.country country 
    LEFT JOIN car.model model 
    LEFT JOIN car.brand brand 
    WHERE EXISTS( 
        SELECT c.id FROM Occasions\Model\Bo\Car AS c 
        JOIN c.options o WHERE o.id IN (2,4,5,51) 
        AND c.id = car.id GROUP BY c.id HAVING COUNT(o.id) >= 4)
    AND brand.id = :brandid 
    AND model.id = :modelid
    AND country.country_code = :countrycountry_code
    
    点赞 打赏 评论

相关推荐 更多相似问题