Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-3196

Enabling LIMIT resp. setMaxResults on subquery

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Won't Fix
    • Affects Version/s: 2.4.2
    • Fix Version/s: None
    • Component/s: None
    • Security Level: All
    • Environment:
      PHP, Zend Framework2, DoctrineModule, DoctrineORMModule

      Description

      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.

        Activity

        Hide
        Christophe Coevoet added a comment -

        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

        Show
        Christophe Coevoet added a comment - 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
        Hide
        webDEVILopers added a comment -

        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.

        Show
        webDEVILopers added a comment - 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.
        Hide
        Christophe Coevoet added a comment -

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

        Show
        Christophe Coevoet added a comment - OK, but what about other platforms ? If only MySQL support this, we cannot integrate the feature in DQL
        Hide
        webDEVILopers added a comment -

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

        Show
        webDEVILopers added a comment - I can't speak for other platforms but I will do some research. Maybe some experts will comment too. Thanks so far.
        Hide
        webDEVILopers added a comment -

        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

        Show
        webDEVILopers added a comment - 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
        Hide
        Marco Pivetta added a comment -

        The parser is not really designed for extensions.

        Additionally, as Christophe Coevoet said, it's not really possible to support LIMIT in all platforms.

        Show
        Marco Pivetta added a comment - The parser is not really designed for extensions. Additionally, as Christophe Coevoet said, it's not really possible to support LIMIT in all platforms.

          People

          • Assignee:
            Marco Pivetta
            Reporter:
            webDEVILopers
          • Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: