How to convert this to Doctrine 2 QueryBuilder format?

Question

I would like to write nested query with Doctrine; Sample SQL is like below:

SELECT * FROM layer WHERE layer.id NOT IN 
               (SELECT task_id FROM users_tasks WHERE user_id = 1) 
AND parent_id IS NOT NULL AND leaf IS TRUE

I have problem converting the SECOND SELECT statement.

Thanks in advance.

I would like to make it with createQueryBuilder not createQuery.

score 38 · Accepted Answer · edited Aug 31 '18 at 16:46

38

try this :

1) create your subquery

$subquery = $this->_em->createQueryBuilder()
    ->select('t.id')
    ->from('yourBundle:Task', 't')
    ->innerjoin('t.user','u')
    ->where('u.id = 1')
    ->getDQL();

2) after create your query

$query = $this->_em->createQueryBuilder();
$query->select('l')
      ->from('yourBundle:Layer', 'l')
      ->where($query->expr()->notIn('l.id', $subquery))
      ....;

I tested it and it works :)

edited Aug 31 '18 at 16:46

marbel82

925
1
18
39

answered May 26 '12 at 03:28

a.aitboudad

4,026
23
31

I would like the one with `createQueryBuilder`; – pmoubed May 26 '12 at 14:24
I modify the query (details above) – a.aitboudad May 26 '12 at 18:08
had some problems. I get Invalid parameter number: number of bound variables does not match number of tokens at ... Can you check this code and let me know where I am gong wrong. https://www.evernote.com/shard/s338/sh/70b0db2c-a1f8-4c82-ac87-8f6b934be18d/4128fff89622337fa80e7f5aa49c26e7 – Roydon D' Souza Dec 02 '15 at 11:09
2

@RoydonD'Souza subquery doesn't take account to parameter (getDQL) you should pass parameters to the $qb instead – a.aitboudad Dec 02 '15 at 12:00
@a.aitboudad Thanks a lot. Will do that. – Roydon D' Souza Dec 03 '15 at 05:23

How to convert this to Doctrine 2 QueryBuilder format?

1 Answers1

Linked