How to Efficiently Perform a WHERE ... IN Subquery with Doctrine 2?

Optimizing WHERE ... IN Subqueries in Doctrine 2

Retrieving order items associated with orders containing a specific item presents a challenge within the Doctrine 2 framework. A straightforward SQL solution looks 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

This SQL query efficiently achieves the desired result. However, replicating this directly in Doctrine 2's query builder requires a more nuanced approach:

$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);

This Doctrine 2 code provides a functional equivalent. Note that model-specific adjustments might be necessary. Areas for potential refinement include optimizing the IN clause's performance with subqueries, exploring alternatives to improve efficiency, and potentially consolidating the two parameters into one for better readability. This example serves as a foundation for creating a robust and efficient solution.

The above is the detailed content of How to Efficiently Perform a WHERE ... IN Subquery with Doctrine 2?. For more information, please follow other related articles on the PHP Chinese website!