SQL 在 Doctrine 2 中执行 WHERE .. IN 子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6637506/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Doing a WHERE .. IN subquery in Doctrine 2
提问by chiborg
I'd like to select order items from all orders with a specific item. In SQL I'd do it like this:
我想从具有特定项目的所有订单中选择订单项目。在 SQL 中，我会这样做：
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?
我将如何使用查询构建器执行此查询？
回答by faken
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, hereand 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.
- 限制：这在 Doctrine 2 中有点问题，似乎查询构建器不太擅长接受限制。看看这里，这里和这里。
- IN 子句通常与数组一起使用，但我认为它可以与子查询一起使用。
- 您可能可以使用相同的参数 ?1，而不是两个参数（因为它们的值相同），但我不确定。
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.
最后，这可能不会第一次奏效，但肯定会让你走上正轨。事后请告诉我们最终的 100% 正确答案。
回答by user1370289
Just to avoid confusion of the last comment posted by clang1234.
只是为了避免混淆 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\Funcis 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.phpmethod getInExpressionfor more details, the array get imploded into IN())
DQL 查询示例确实有效。The 确实expr->in()会将第二个参数转换为数组，在本例中为 DQL 字符串。它的作用是创建一个以 DQL 查询字符串作为第一个元素的数组。这正是 theExpr\Func正在等待的，一个数组。在 Doctrine 2 代码中更深入一点，dql 查询字符串数组元素将被正确管理。（有关更多详细信息，请参阅DBAL/Platforms/AbstractPlatform.php方法getInExpression，数组被内爆为IN()）
