'where not in' query with doctrine query builder

Question

Im trying to reproduce this query:

SELECT * FROM `request_lines`
where request_id not in(
select requestLine_id from `asset_request_lines` where asset_id = 1 
)

in doctrine query builder, I am stuck on the where request_id not in(select

I currently have:

$linked = $em->createQueryBuilder()
        ->select('rl')
        ->from('MineMyBundle:MineRequestLine', 'rl')
        ->where()
        ->getQuery()
        ->getResult();

Lupita

Answer 1

You need to use query builder expressions, and this means you need access to the query builder object. Also, the code is easier to write if you generate the subselect list ahead of time:

$qb = $em->createQueryBuilder();

$nots = $qb->select('arl')
          ->from('$MineMyBundle:MineAssetRequestLine', 'arl')
          ->where($qb->expr()->eq('arl.asset_id',1))
          ->getQuery()
          ->getResult();

$linked = $qb->select('rl')
             ->from('MineMyBundle:MineRequestLine', 'rl')
             ->where($qb->expr()->notIn('rl.request_id', $nots))
             ->getQuery()
             ->getResult();

Hermes

Is this not possible in one query? – Kame

Yea, use Wilt's approach – Hermes

Answer 2

It is possible to do this in one Doctrine query:

$qb  = $this->_em->createQueryBuilder();
$sub = $qb;

$sub = $qb->select('arl')
          ->from('$MineMyBundle:MineAssetRequestLine', 'arl')
          ->where($qb->expr()->eq('arl.asset_id',1));

$linked = $qb->select('rl')
             ->from('MineMyBundle:MineRequestLine', 'rl')
             ->where($qb->expr()->notIn('rl.request_id',  $sub->getDQL()))
             ->getQuery()
             ->getResult();

Check the reference in this answer here

Kame

Frankly, I prefer this answer to my own – Hermes

Great solution, thanks! This same approach can be used where you would logically expect ->where($qb->expr()->eq('x1.some_id', $sub->getDql())) to work, but the ->eq(...) or ->neq(...) expects a literal. Just use ->in(...) or ->notIn(...) and limit the results of the subquery to a single return value. – Horribly

Probably obvious but caught me out briefly - if your subquery has parameters make sure you set them on the main QB not the sub QB as they will not be included in the final query when calling $sub->getDQL() – Chokebore

I had to create two different query builders as using $qb on both queries would give me "arl already defined". – Dissever

Answer 3

Using Symfony 5, this solution might help those, who are trying to set parameters on a subquery, the notIn() 2nd argument accepts an array or you could pass a DQL instead and that's what we are doing here and keep in mind that the parameters should be added to the main query as below.

$main  = $this->em->createQueryBuilder();
$sub = $main;

$sub = $sub->select('arl')
          ->from('$MineMyBundle:MineAssetRequestLine', 'arl')
          ->where($sub->expr()->eq('arl.asset_id',':id'));

$linked = $main->select('rl')
             ->from('MineMyBundle:MineRequestLine', 'rl')
             ->where($main->expr()->notIn('rl.request_id',  $sub->getDQL()))
             ->setParameter('id', 1)
             ->getQuery()
             ->getResult();

Estriol