Doing a WHERE .. IN subquery in Doctrine 2

Solution 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.

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