Doctrine 2 limit IN subquery

Question

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 !

yes, you could create 2 query builders, one for the IN subselect, and one for the global DQL. in the first one, use `setMaxResults`. — Florian Klein, Mar 05 '13 at 20:45
Seems that main reason for limit not included in DQL is caching - http://www.doctrine-project.org/jira/browse/DDC-885 — gregor, May 22 '14 at 16:29
Here http://stackoverflow.com/questions/24068947/subquery-with-limit-in-doctrine could be a solution — Serhii Smirnov, Nov 07 '16 at 16:20
See my answer on this similar question: https://stackoverflow.com/a/46663949/146738 — Simon Epskamp, Oct 12 '17 at 11:18

score 20 · Accepted Answer · answered Jul 27 '14 at 04:27

20

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.

answered Jul 27 '14 at 04:27

klandaika

357
3
11

1

This is really unfortunate. I was looking at my code for an hour trying to figure out what's wrong. Doctrine could've at least throw an exception saying that it's unsupported. – IluTov Dec 02 '15 at 10:14
Marking this as answer because while it didn't help, it did confirm my doubts that it just can't be done. – Growiel Dec 04 '15 at 07:43
1

Here http://stackoverflow.com/questions/24068947/subquery-with-limit-in-doctrine could be a solution – Serhii Smirnov Nov 07 '16 at 16:18

Doctrine 2 limit IN subquery

1 Answers1

Linked

Related