php - 交响乐 3 : Select with relation many to many

Question

我有两个具有多对多关系的实体(汽车和经销商)。我有一个有效的 SQL 请求:

Select c.id, c.nom FROM car as c WHERE   c.id NOT IN  
(Select car_id FROM dealer_car where dealer_id =  16);

但是使用 querybuilder 我不能这样做，因为 dealer_car 是多对多关系的表。

实际上我有这个查询生成器返回确切的反对

'query_builder' => function(EntityRepository $er) use ($options){
                    return $er->createQueryBuilder('c')
                        ->innerJoin('c.dealer','d')
                        ->andWhere('c.id  NOT IN (Select d.id FROM Bundle:Dealer de where de.id = :id)')
                        ->setParameter('id',$options['data']->getId());
                }

EDIT

我的数据库中有以下数据

Car
1;BMW
2;Tesla
3;Mercedes
4;Toyota

dealer_car
16;2
16;3

dealer
1;Johnny
2;David
16;Nelson

而下面的查询结果为空

select c.id, c.name,d.name from car c join dealer_car dc on c.car_id=c.id join dealer d on dc.d_id=d.id where d.id!=16

Answer 1

如何更改您的查询？

SELECT c.id, c.nom FROM car AS c LEFT OUTER JOIN dealer_car AS dc ON c.id = dc.car_id AND dc.dealer_id = 16 WHERE dc.dealer_id IS NULL;