Doctrine 2 限制 IN 子查询 [英] Doctrine 2 limit IN subquery
问题描述
我正在尝试在 Doctrine2 的 IN 语句中使用子查询.
I'm trying to use a subquery in a IN statement in Doctrine2.
原始 SQL 查询应如下所示:
Here's what the raw SQL query should look like :
SELECT * FROM license
WHERE id
IN (SELECT id
FROM license
WHERE subscription = x
ORDER BY date DESC
LIMIT 5)
ORDER BY name ASC;
我想要做的是显示按名称排序的最后5个结果,所以我必须先查询最后5个结果,然后在主查询中按名称排序.
What I want to do is display the 5 last results ordered by name, so I have to first query the last 5 results and then order by name in the main query.
问题是我似乎无法限制内部查询.
The problem is that I can't seem to LIMIT the inner query.
这是我当前的代码:
$qb = $this->createQueryBuilder('l');
$qb->select('l.id');
$qb = $this->whereSubscriptionId($qb, $subscription_id);
$qb = $this->offsetLimitOrder($qb, 0, 5, 'deliveryDatetime desc');
//Second Query, adds the "order by X"
$qb2 = $this->createQueryBuilder('l2');
$qb2->add('where', $qb2->expr()->in('l2.id', $qb->getQuery()->getDQL()));
if(isset($order)){
$order = explode(' ', $order);
$qb2->addOrderBy('l2.'.$order[0], $order[1]);
}
return $qb2->getQuery()
->getResult();
如您所见,我创建了第一个查询,对它进行了排序和限制(通过自定义方法),然后尝试在第二个查询中使用它.
As you can see, I create my first query, I order and limit it (via a custom method) and then I try to use it in the second query.
然而,似乎 LIMIT 不是 DQL 语句的一部分,因为当我 var_dump 第一个查询的 DQL 时,LIMIT 不存在,这意味着当我运行 $qb2->getQuery()->getResult 时它被完全忽略();
However, it seems that the LIMIT is not part of the DQL statement because when I var_dump the first query's DQL, the LIMIT is absent, which means that it's completly ignored when I run $qb2->getQuery()->getResult();
我通过启动第一个查询并在第二个查询中手动输入结果使其工作,但它很难看.
I made it work by launching the first query and manually inputing the results in the second one, but it's ugly.
知道如何正确操作吗?
谢谢!
推荐答案
遗憾的是 Doctrine 不支持对嵌套查询的限制.即使您在内部 QueryBuilder 上使用 2 个 QueryBuilders 和 setMaxResults(),它也会被忽略.
Unfortunately Doctrine does not support limit on nested queries. Even if you use 2 QueryBuilders and setMaxResults() on the inner QueryBuilder, it will simply be ignored.
此时执行此操作的唯一方法是运行 2 个单独的查询.
The only way to do this at this time is to run 2 individual queries.
这篇关于Doctrine 2 限制 IN 子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!