在doctrine 2中的子查询中执行WHERE .. [英] Doing a WHERE .. IN subquery in Doctrine 2

问题描述

我想从特定商品的所有订单中选择订单商品。在SQL中我将这样做：

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?

推荐答案

这是我会尝试它：

$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 didn't test this of course, and made some assumptions about your models. Possible problems:

• 限制：这在Doctrine 2中有些问题，似乎查询生成器不能很好地接受限制。请查看此处，此处和此处。


• IN子句通常与数组一起使用，但我认为它将与子查询一起使用。


• 您可能可以使用相同的参数？1，而不是两个参数（因为它们是相同的值），但我不确定。

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

总而言之，这可能不是第一次，但一定会把你在正确的轨道。请告诉我们最终的100％正确答案。

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.

这篇关于在doctrine 2中的子查询中执行WHERE ..的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！