Orm: DDC-2891: Impossible to pass a limit to a subquery

[doctrinebot]

Jira issue originally created by user johnconnor:

It seems that passing the limit to a subquery is not working

$subquery = $em->createQueryBuilder()->from('...')->where('...')->setMaxResults(5);
$query = $em->createQueryBuilder()->from('...')->where(
   $qb->expr()->in('p.id', $subquery->getDQL())
);
$query->getQuery()->getResult();

The query works but the is no specified limit in the resulting SQL.
I am aware that DQL does not support the limits and offsets, so i guess there should be another way to get this working?

[doctrinebot]

Comment created by @deeky666:

Can you please tell which database platform you are using? Because limiting results heavily depends on the platform used.

[doctrinebot]

Comment created by johnconnor:

MySql

[doctrinebot]

Comment created by @deeky666:

Hmmm I am not quite sure if the limit/offset is invoked for subqueries but I don't see why it shouldn't. Also I think this is not a DBAL issue because the limit/offset support for MySQL is the easiest we have on all platform. See: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/MySqlPlatform.php#L51-L63
The query doesn't have to be modified but instead only the limit clause is appended to the query. Can you maybe provide the generated SQL for that query?

[doctrinebot]

Comment created by johnconnor:

I think if you try to build any query with QueryBuilder, set a limit to it with setMaxResults then call getDQL method, you should see that the output contains no info about limit.
So if you look at my code example , at $qb->expr()->in('p.id', $subquery->getDQL()), then you will see that the getDQL passes to the IN expression a query which already DOES NOT have limit. So this is the place where any info about limits and offsets gets lost.

So I fail to see what it has to do with any specific db engine,however I can provide the mysql resulting query if you want,though it looked perfectly normal to me,just lacks the LIMIT part.

[cesardmoro]

You any news on this ? please maybe a hack ?

[vladimmi]

Just to note - it's still impossible to use subqueries with limits.