DDC-3196: Enabling LIMIT resp. setMaxResults on subquery #3881

New Issue

admin · 2026-01-22T14:30:15+01:00

admin commented

2026-01-22 14:30:15 +01:00

Originally created by @doctrinebot on GitHub (May 19, 2014).

Originally assigned to: @Ocramius on GitHub.

Jira issue originally created by user webdevilopers:

The following subquery using LIMIT 1 is legal SQL:

(SELECT state FROM contract_states 
WHERE contract*states.contract*id = contracts.id
ORDER BY date DESC, created_at DESC, id DESC LIMIT 1) = ?

I tried to get the same result in DQL using the queryBuilder:

    $qb2 = $this->_em->createQueryBuilder();
        $qb2->select(array('s2.state'))
            ->from('Application\Entity\ContractState', 's2')
            ->where('s2.contract = c.id')
            ->orderBy('s2.date', 'DESC')
            ->addOrderBy('s2.createdAt', 'DESC')
            ->addOrderBy('s2.id', 'DESC')
            ->setMaxResults(1);
$stateDql = $qb2->getDQL();

or directely via DQL:

        $stateDql = 'SELECT s2.state FROM Application\Entity\ContractState s2 WHERE s2.contract = c.id
                ORDER BY s2.date DESC, s2.createdAt DESC, s2.id DESC LIMIT 1';

The DQL was inserted in my query:

        $qb->select(
            array(
                'DISTINCT(c.id) AS contract_id',
                $qb->expr()->max('s.createdAt') . ' AS state_updated',
                's.createdAt',
                's.state'
            ))
            ->from('Application\Entity\Contract', 'c')
            ->join('c.states', 's')
->andWhere('s.state = (' . $stateDql . ')');

The first DQL string did not include a LIMIT part. The second one ended with the following error:
Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got 'LIMIT'

Is there a way to achieve it?

I also tried a workaround by using MAX inside my orderBy which could help:

->addOrderBy($qb->expr()->max('s2.createdAt'), 'DESC')

But this throws an error too:
Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got '('

I hope this is the right place to post the issue. I havn't found a similar topic browsing 'subquery' or 'LIMIT' as keyword.

Originally created by @doctrinebot on GitHub (May 19, 2014). Originally assigned to: @Ocramius on GitHub. Jira issue originally created by user webdevilopers: The following subquery using LIMIT 1 is legal SQL: ``` sql (SELECT state FROM contract_states WHERE contract*states.contract*id = contracts.id ORDER BY date DESC, created_at DESC, id DESC LIMIT 1) = ? ``` I tried to get the same result in DQL using the queryBuilder: ``` $qb2 = $this->_em->createQueryBuilder(); $qb2->select(array('s2.state')) ->from('Application\Entity\ContractState', 's2') ->where('s2.contract = c.id') ->orderBy('s2.date', 'DESC') ->addOrderBy('s2.createdAt', 'DESC') ->addOrderBy('s2.id', 'DESC') ->setMaxResults(1); $stateDql = $qb2->getDQL(); ``` or directely via DQL: ``` $stateDql = 'SELECT s2.state FROM Application\Entity\ContractState s2 WHERE s2.contract = c.id ORDER BY s2.date DESC, s2.createdAt DESC, s2.id DESC LIMIT 1'; ``` The DQL was inserted in my query: ``` $qb->select( array( 'DISTINCT(c.id) AS contract_id', $qb->expr()->max('s.createdAt') . ' AS state_updated', 's.createdAt', 's.state' )) ->from('Application\Entity\Contract', 'c') ->join('c.states', 's') ->andWhere('s.state = (' . $stateDql . ')'); ``` The first DQL string did not include a LIMIT part. The second one ended with the following error: **Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got 'LIMIT'** Is there a way to achieve it? I also tried a workaround by using MAX inside my orderBy which could help: ``` ->addOrderBy($qb->expr()->max('s2.createdAt'), 'DESC') ``` But this throws an error too: **Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got '('** I hope this is the right place to post the issue. I havn't found a similar topic browsing 'subquery' or 'LIMIT' as keyword.

admin added the Improvement label 2026-01-22 14:30:15 +01:00

admin closed this issue

2026-01-22 14:30:16 +01:00

admin commented

2026-01-22 14:30:16 +01:00

@doctrinebot commented on GitHub (May 19, 2014):

Comment created by stof:

There is not LIMIT in DQL. the mx result is not part of the DQL string.

However, is limiting a subquery valid in SQL generally or only on some platforms ?

I know that MySQL supports them only in some subqueries but not all places where subqueries can be used: http://dev.mysql.com/doc/refman/5.6/en/subquery-restrictions.html

@doctrinebot commented on GitHub (May 19, 2014): Comment created by stof: There is not `LIMIT` in DQL. the mx result is not part of the DQL string. However, is limiting a subquery valid in SQL generally or only on some platforms ? I know that MySQL supports them only in some subqueries but not all places where subqueries can be used: http://dev.mysql.com/doc/refman/5.6/en/subquery-restrictions.html

admin commented

2026-01-22 14:30:17 +01:00

@doctrinebot commented on GitHub (May 19, 2014):

Comment created by webdevilopers:

Thanks for the quick reply.

I think it is not valid on *correlated subquery* and using them inside the select statement.

The subquery mentioned above is used inside the *where* statement and will return a legal result in the current MySQL version.

Maybe there is another workaround? As I described setting a *max* expression in the order clause did not work out either.

@doctrinebot commented on GitHub (May 19, 2014): Comment created by webdevilopers: Thanks for the quick reply. I think it is not valid on **correlated subquery\* and using them inside the *select** statement. The subquery mentioned above is used inside the **where\* statement and will return a legal result in the current *MySQL** version. Maybe there is another workaround? As I described setting a **max\* expression in the *order** clause did not work out either.

admin commented

2026-01-22 14:30:17 +01:00

@doctrinebot commented on GitHub (May 19, 2014):

Comment created by stof:

OK, but what about other platforms ? If only MySQL support this, we cannot integrate the feature in DQL

@doctrinebot commented on GitHub (May 19, 2014): Comment created by stof: OK, but what about other platforms ? If only MySQL support this, we cannot integrate the feature in DQL

admin commented

2026-01-22 14:30:18 +01:00

@doctrinebot commented on GitHub (May 20, 2014):

Comment created by webdevilopers:

I can't speak for other platforms but I will do some research.
Maybe some experts will comment too.
Thanks so far.

@doctrinebot commented on GitHub (May 20, 2014): Comment created by webdevilopers: I can't speak for other platforms but I will do some research. Maybe some experts will comment too. Thanks so far.

admin commented

2026-01-22 14:30:18 +01:00

@doctrinebot commented on GitHub (May 21, 2014):

Comment created by webdevilopers:

While I am doing some research can somebody tell if it s possible to male DQL accept the LIMIT by creating a custom extension for the Lexer? E.g.:
https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/GroupConcat.php

@doctrinebot commented on GitHub (May 21, 2014): Comment created by webdevilopers: While I am doing some research can somebody tell if it s possible to male DQL accept the LIMIT by creating a custom extension for the Lexer? E.g.: https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/GroupConcat.php

admin commented

2026-01-22 14:30:19 +01:00

@doctrinebot commented on GitHub (Jun 26, 2014):

Comment created by @ocramius:

The parser is not really designed for extensions.

Additionally, as [~stof] said, it's not really possible to support LIMIT in all platforms.

@doctrinebot commented on GitHub (Jun 26, 2014): Comment created by @ocramius: The parser is not really designed for extensions. Additionally, as [~stof] said, it's not really possible to support `LIMIT` in all platforms.

admin commented

2026-01-22 14:30:19 +01:00

@doctrinebot commented on GitHub (Jun 26, 2014):

Issue was closed with resolution "Won't Fix"

@doctrinebot commented on GitHub (Jun 26, 2014): Issue was closed with resolution "Won't Fix"

admin commented

2026-01-22 14:30:19 +01:00

@doctrinebot commented on GitHub (Oct 22, 2015):

Comment created by przemo_li:

According to this:
http://www.postgresql.org/docs/8.4/static/sql-select.html

Postgres support at least LIMIT in sub select as long as its inside parenthesis.

Can someone specify which othere dbms need to support SQL feature for inclusion in doctrine?

@doctrinebot commented on GitHub (Oct 22, 2015): Comment created by przemo_li: According to this: http://www.postgresql.org/docs/8.4/static/sql-select.html Postgres support at least LIMIT in sub select as long as its inside parenthesis. Can someone specify which othere dbms need to support SQL feature for inclusion in doctrine?

admin commented

2026-01-22 14:30:19 +01:00

@devkpv commented on GitHub (Dec 30, 2015):

Hi!
I have same problem. Can you provide workaround for this? Probably i can extend some classes and inject it?
I'm MySQL user.

@devkpv commented on GitHub (Dec 30, 2015): Hi! I have same problem. Can you provide workaround for this? Probably i can extend some classes and inject it? I'm MySQL user.

admin commented

2026-01-22 14:30:20 +01:00

@Ocramius commented on GitHub (Dec 30, 2015):

Can you provide workaround for this?

If you really need a LIMIT in a subquery, then you probably already reached the limits of DQL and are going into platform-specific SQL syntax: use NativeSQL for that.

@Ocramius commented on GitHub (Dec 30, 2015): > Can you provide workaround for this? If you _really_ need a `LIMIT` in a subquery, then you probably already reached the limits of DQL and are going into platform-specific SQL syntax: use NativeSQL for that.

admin commented

2026-01-22 14:30:20 +01:00

@devkpv commented on GitHub (Dec 30, 2015):

Ok, thanks.

@devkpv commented on GitHub (Dec 30, 2015): Ok, thanks.

admin commented

2026-01-22 14:30:20 +01:00

@krisstwo commented on GitHub (Mar 19, 2021):

This may not solve all the LIMIT n, m cases but this SO answer is a good start. I used a custom dql function to achieve this.

Same solution is referenced here also : https://github.com/doctrine/orm/issues/5409

@krisstwo commented on GitHub (Mar 19, 2021): This may not solve all the LIMIT n, m cases but this [SO answer](https://stackoverflow.com/a/46663949/4609860) is a good start. I used a custom dql function to achieve this. Same solution is referenced here also : https://github.com/doctrine/orm/issues/5409

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#3881