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?

Will B. · Accepted Answer · 2018-11-05 19:50:01Z

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.

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
Thanks for the corrections. This will be a very useful reference for everybody using Doctrine 2 QueryBuilder. Best regards
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.
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'.
Notice that there are two querybuilders used in the example. This should save the next guy some time...

Kate Orlova · Accepted Answer · 2020-02-27 14:29:49Z

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

Collectives™ on Stack Overflow

Doing a WHERE .. IN subquery in Doctrine 2

2 Answers 2

11 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Linked

Related