Doctrine QueryBuilder查询：具有多个关联

Question

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.

Answer 1

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