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

Closed
doctrinebot opened this Issue May 19, 2014 · 11 comments

3 participants

@doctrinebot

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.

@doctrinebot

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

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

Comment created by stof:

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

@doctrinebot

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

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

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

Issue was closed with resolution "Won't Fix"

@doctrinebot

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?

@Ocramius Ocramius was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@SpiLLeR

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

@Ocramius
Doctrine member

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.

@SpiLLeR

Ok, thanks.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment