'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();

Lighthart · Accepted Answer · 2014-06-03T14:58:55.057

50

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();

edited Jun 03 '14 at 14:58

answered Dec 19 '12 at 17:47

Lighthart

3,648
6
28
47

1

Is this not possible in one query? – Wilt Nov 05 '15 at 11:16
1

Yea, use Wilt's approach – Lighthart Dec 01 '15 at 21:37

score 42 · Answer 2 · edited May 23 '17 at 12:10

42

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

edited May 23 '17 at 12:10

Community

1
1

answered Nov 05 '15 at 11:20

Wilt

41,477
12
152
203

10

Frankly, I prefer this answer to my own – Lighthart Dec 01 '15 at 21:36
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. – iisisrael Jun 28 '16 at 20:16
6

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()` – Chris Brown Feb 20 '17 at 10:49
1

I had to create two different query builders as using $qb on both queries would give me "arl already defined". – Select0r Mar 10 '21 at 11:49

score 0 · Answer 3 · answered Aug 23 '22 at 11:47

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();

'where not in' query with doctrine query builder

3 Answers3

Linked