Executing WHERE .. In a subquery in Doctrine 2

Question

I would like to select order items from all orders with a specific item. In SQL, I would 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 do I execute this query with the query builder?

Answer 1

Here's how I try:

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

I did not check this, of course, and made some assumptions about your models. Possible problems:

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

In conclusion, this may not work the first time, but it will certainly lead you to the right path. After that, tell us the final 100% correct answer.

Answer 2

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

The dql query example really works. It is true that the expression expr-> in () will pass the second parameter to the array, in this case the dql string. What he does is he simply creates an array with the dql query string as the first element. This is what Expr \ Func expects, an array. It is a bit deeper in Doctrine 2 code that the element of the array of dql query elements will be properly managed. (see DBAL / Platforms / AbstractPlatform.php getInExpression method for more details, the array is inserted into IN ())