DDC-2891: Impossible to pass a limit to a subquery #3606

New Issue

admin · 2026-01-22T14:23:39+01:00

admin commented

2026-01-22 14:23:39 +01:00

Originally created by @doctrinebot on GitHub (Jan 7, 2014).

Originally assigned to: @beberlei on GitHub.

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?

Originally created by @doctrinebot on GitHub (Jan 7, 2014). Originally assigned to: @beberlei on GitHub. 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?

admin added the Improvement label 2026-01-22 14:23:39 +01:00

admin commented

2026-01-22 14:23:40 +01:00

@doctrinebot commented on GitHub (Jan 8, 2014):

Comment created by @deeky666:

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

@doctrinebot commented on GitHub (Jan 8, 2014): Comment created by @deeky666: Can you please tell which database platform you are using? Because limiting results heavily depends on the platform used.

admin commented

2026-01-22 14:23:40 +01:00

@doctrinebot commented on GitHub (Jan 8, 2014):

Comment created by johnconnor:

MySql

@doctrinebot commented on GitHub (Jan 8, 2014): Comment created by johnconnor: MySql

admin commented

2026-01-22 14:23:40 +01:00

@doctrinebot commented on GitHub (Jan 8, 2014):

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 commented on GitHub (Jan 8, 2014): 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?

admin commented

2026-01-22 14:23:41 +01:00

@doctrinebot commented on GitHub (Jan 8, 2014):

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.

@doctrinebot commented on GitHub (Jan 8, 2014): 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.

admin commented

2026-01-22 14:23:41 +01:00

@cesardmoro commented on GitHub (Mar 28, 2017):

You any news on this ? please maybe a hack ?

@cesardmoro commented on GitHub (Mar 28, 2017): You any news on this ? please maybe a hack ?

admin commented

2026-01-22 14:23:41 +01:00

@vladimmi commented on GitHub (Jun 15, 2018):

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

@vladimmi commented on GitHub (Jun 15, 2018): Just to note - it's still impossible to use subqueries with limits.

admin commented

2026-01-22 14:23:42 +01:00

@adrienpayen commented on GitHub (Aug 22, 2021):

New note 3 years later, it is still impossible to use subqueries with limits. Any workaround ?

@adrienpayen commented on GitHub (Aug 22, 2021): New note 3 years later, it is still impossible to use subqueries with limits. Any workaround ?

admin commented

2026-01-22 14:23:42 +01:00

@greg0ire commented on GitHub (Aug 22, 2021):

@adrienpayen @vladimmi if I had a contribution graph like yours, I would refrain from complaining about a lack of contributions. You've done nothing to fix an issue that directly impacts you, so why would you expect people that are not impacted to do the work for you? Please stop the spamming and start being constructive.

You could provide a failing test case, or use a debugger to pinpoint where the issue is.

@greg0ire commented on GitHub (Aug 22, 2021): @adrienpayen @vladimmi if I had a contribution graph like yours, I would refrain from complaining about a lack of contributions. You've done nothing to fix an issue that directly impacts you, so why would you expect people that are not impacted to do the work for you? Please stop the spamming and start being constructive. You could provide a failing test case, or use a debugger to pinpoint where the issue is.

admin commented

2026-01-22 14:23:42 +01:00

@vladimmi commented on GitHub (Aug 23, 2021):

@greg0ire If I was such a tender snowflake, I would refrain from using public services having comments - especially bug trackers. And especially I would at least read ticket before posting anything there - because "failing case" was provided 7 years ago in the very first message and "pinpointed where the issue is" in the same day several messages later.

@vladimmi commented on GitHub (Aug 23, 2021): @greg0ire If I was such a tender snowflake, I would refrain from using public services having comments - especially bug trackers. And especially I would at least read ticket before posting anything there - because "failing case" was provided 7 years ago in the very first message and "pinpointed where the issue is" in the same day several messages later.

admin commented

2026-01-22 14:23:42 +01:00

@greg0ire commented on GitHub (Aug 23, 2021):

Well then you can move on to the next step: fix it!
If I had to read every thread when people ask for news, I wouldn't be able to make any progress. That being said, the first comment is not really what I meant by failing test case. You can still work on that before attempting to fixing the bug. Make a PR with the code snippet above in a PHPUnit test.

tender snowflake

Hmmmm… I think the discussion is too heated, don't you agree?

@greg0ire commented on GitHub (Aug 23, 2021): Well then you can move on to the next step: fix it! If I had to read every thread when people ask for news, I wouldn't be able to make any progress. That being said, the first comment is not really what I meant by failing _test_ case. You can still work on that before attempting to fixing the bug. Make a PR with the code snippet above in a PHPUnit test. > tender snowflake Hmmmm… I think the discussion is too heated, don't you agree?

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#3606