原则2限制IN子查询 [英] Doctrine 2 limit IN subquery
问题描述
以下是原始SQL查询应该如下所示:
SELECT * FROM license
WHERE id
IN(SELECT id
FROM license
WHERE subscription = x
ORDER BY日期DESC
限制5)
订单BY名称ASC;
我想做的是显示按名称排序的最后5个结果,所以我必须先查询最后5个结果,然后按主要查询的名称排序。
问题是我似乎无法限制内部查询。
这是我现在的代码:
$ qb = $ this-> createQueryBuilder );
$ qb-> select('l.id');
$ qb = $ this-> whereSubscriptionId($ qb,$ subscription_id);
$ qb = $ this-> offsetLimitOrder($ qb,0,5,'deliveryDatetime desc');
//第二个查询,添加按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();
如您所见,我创建了我的第一个查询,我订购并限制(通过自定义方法)然后我尝试在第二个查询中使用它。
但是,似乎LIMIT不是DQL语句的一部分,因为当我将var_dump第一个查询的DQL ,则LIMIT不存在,这意味着当我运行$ qb2-> getQuery() - > getResult();
时,它完全被忽略,通过启动第一个查询并手动输入第二个结果,但是它很难看。
任何关于如何正确执行的想法?
谢谢!
不幸的是,Doctrine不支持嵌套查询的限制。即使在QueryBuilder的内部使用了2个QueryBuilders和setMaxResults(),它也将被忽略。
此时唯一的方法是运行2个单独的查询。
I'm trying to use a subquery in a IN statement in Doctrine2.
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;
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.
Here's my current code :
$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.
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.
Any idea on how to do it properly ?
Thanks !
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.
The only way to do this at this time is to run 2 individual queries.
这篇关于原则2限制IN子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!