Doing a WHERE .. IN subquery in Doctrine 2

Question

I'd like to select order items from all orders with a specific item. In SQL I'd do it like this:

SELECT DISTINCT i.id, i.name, order.name 
FROM items i 
JOIN orders o ON i.order_id=o.id 
WHERE o.id IN (
   SELECT o2.id FROM orders o2
   JOIN items i2 ON i2.order_id=o2.id AND i2.id=5
)
AND i.id != 5
ORDER BY o.orderdate DESC
LIMIT 10

How would I do this query with the query builder?

Supat

Answer 1

This is how I would try it:

/** @var Doctrine\ORM\EntityManager $em */
$expr = $em->getExpressionBuilder();
$em->createQueryBuilder()
   ->select(array('DISTINCT i.id', 'i.name', 'o.name'))
   ->from('Item', 'i')
   ->join('i.order', 'o')
   ->where(
       $expr->in(
           'o.id',
           $em->createQueryBuilder()
               ->select('o2.id')
               ->from('Order', 'o2')
               ->join('Item', 
                      'i2', 
                      \Doctrine\ORM\Query\Expr\Join::WITH, 
                      $expr->andX(
                          $expr->eq('i2.order', 'o2'),
                          $expr->eq('i2.id', '?1')
                      )
               )
               ->getDQL()
       )
   )
   ->andWhere($expr->neq('i.id', '?2'))
   ->orderBy('o.orderdate', 'DESC')
   ->setParameter(1, 5)
   ->setParameter(2, 5)
   ;

I didn't test this of course, and made some assumptions about your models. Possible problems:

• Limit: this has been somewhat of a problem in Doctrine 2, it seems query builder is not very good at accepting limits. Do take a look here, here and here.
• The IN clause is usually used with an array, but I think it will work with a subquery.
• You probably can use the same parameter ?1, instead of two parameters (because they're the same value), but I'm not sure.

Concluding, this may not work first time, but will surely put you on the right track. Do tell us the final 100% correct answer afterwards.

Hatred

Thanks! There were only two things missing from your example: The Namespace needs a Backslash at the beginning and the subquery needs to be given as a string with the getDQL() method. I've edited your example to correct this – Supat

Thanks for the corrections. This will be a very useful reference for everybody using Doctrine 2 QueryBuilder. Best regards – Hatred

This solution doesn't seem to work in Doctrine2.0. Doctrine\ORM\Query\Expr->in() casts the second argument as an array. If you pass in the DQL, it won't be interpreted. – Swedenborgian

Thank you, helped me! I had trouble because I was assigning parameters to the inner query builder and wondering why the paramater count doesn't match. – Ultan

This can be dangerous if the inner query has some bound parameters, as calling getDql() in my experience nullifies any such bindings. Therefore, one needs to bind parameters with setParameter again in the outer query, otherwise the code will break with 'Invalid parameter number: number of bound variables does not match number of tokens'. – Pathognomy

Notice that there are two querybuilders used in the example. This should save the next guy some time... – Phylis

works ..but still angry to Doctrine counterintuitive QB! :( – Zelig

How do you initialize the 2nd queryBuilder (qb2) ? – Rosemaria

You can initialize the second query builder the same way as the first, $qb2 = $entityManager->createQueryBuilder();. Depending if you execute this code in a controller (you should not!!) or a repository, you should be able to access the entityManager service, respectively from $this->getDoctrine()->getManager() or from $this->_em. – Blackandblue

This answer is very useful. I am using another ORM backed by Doctrine DBAL and am able to build a really complex subquery with tips I got here and it works well. Just use getSQL instead of getDQL if not using DoctrineORM. – Tobitobiah

Is it possible to refer to column from outer query in subquery using doctrine ? I have some use cases for such queries. – Alameda

Answer 2

Just to avoid confusion of the last comment posted by clang1234.

The DQL query example really works. It's true that the The expr->in() will cast the second parameter to an array, in this case the DQL string. What it does, it just create an array with the DQL query string as the first element. That's exactly what the the Expr\Func is waiting for, an array. It's a little deeper in the Doctrine 2 code that the dql query string array element will be managed correctly. (see DBAL/Platforms/AbstractPlatform.php method getInExpression for more details, the array get imploded into IN())

Tartrate