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

Open
doctrinebot opened this Issue Jan 7, 2014 · 4 comments

2 participants

@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.

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment