Symfony 3 : Select with relation many to many

user_8914471 0
Viewed 191

I have two Entity with a relation many to many (car and dealer). I have a working SQL request :

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

But with querybuilder I can't do this because dealer_car is the table of the many to many relation.

Actually I have this query Builder that return the exact oppose

'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

I have the following data in my database 

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

dealer_car
16;2
16;3

dealer
1;Johnny
2;David
16;Nelson

And the result of the following Query is empty 

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
symfony dql php
2 Answers

What about changing your query?

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;
user_41022680

I suppose you are trying to get the cars that are not associated to dealer with id=16. This should work for you.

 $qb->select('c.id,c.nom')
            ->join('c.dealer','d')
            ->where($qb->expr()->neq('d.id',':delearId'))
            ->setParameter('dealerId',$dealerId)
            ->getQuery()
            ->getResult();

the sub query returns the cars associated to dealer id=16. in order to simplify the query you just need to do the join and exclude the id=16 from the dealer. Is more like a direct question

You are requesting something like: give me the cars which are not the same cars the dealer 16 is using

I am requesting something like(directly):give me the cars which the dealer 16 is not using.

Hope it helps

EDIT

I have made these tables: this would be your query:

select c.id, c.carname FROM car c WHERE   c.id NOT IN  
(Select car_id FROM dealer_has_car where dealer_id =  16);

the result:

1;"car1"
2;"car2"
3;"car4"
4;"car3"
7;"car7"
8;"car8"
9;"car9"
10;"car10"

this would be my query

select c.id, c.carname,d.name from car c join dealer_has_car dhc on dhc.car_id=c.id join dealer d on dhc.dealer_id=d.id where d.id!=16

the result

1;"car1";"dealer1"
2;"car2";"dealer1"
3;"car4";"dealer2"
4;"car3";"dealer2"
7;"car7";"dealer3"
8;"car8";"dealer4"
9;"car9";"dealer4"
10;"car10";"dealer5"

as you can see the query works fine and the DQl of my SQL is the one I posted above. Is the same result!!

Could you give more info about your results as I did?

user_88013010
Related