Limiting Subqueries in Doctrine 2 DQL

TIL that Doctrine 2 doesn't support LIMITs within subqueries which can be frustrating. In my case, I wanted to LEFT JOIN on a table using a subquery with a single result - something like this:

$dqb->from('MyAppBundle:Foo', 'foo')
    ->leftJoin('', 'bar', 'WITH', 'bar = (SELECT b FROM MyAppBundle:Bar b WHERE = foo AND b.published_date >= :now ORDER BY t.startDate LIMIT 1)');

But Doctrine kept throwing this error:

Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got 'LIMIT'

To get around this limitation, I ended creating a custom Doctrine function FIRST(). Basically, you wrap a subquery within this function and Doctrine will apply LIMIT 1 for you:

$dqb->from('MyAppBundle:Foo', 'foo')
    ->leftJoin('', 'bar', 'WITH', 'bar = FIRST(SELECT b FROM MyAppBundle:Bar b WHERE = foo AND b.published_date >= :now ORDER BY t.startDate)');

Here's what the EBNF looks like:

FirstFunction ::= "FIRST" "(" Subselect ")"

And here's the class I implemented:


use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\AST\Subselect;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

 * FirstFunction ::=
 *     "FIRST" "(" Subselect ")"
class FirstFunction extends FunctionNode
     * @var Subselect
    private $subselect;

     * {@inheritdoc}
    public function parse(Parser $parser)
        $this->subselect = $parser->Subselect();

     * {@inheritdoc}
    public function getSql(SqlWalker $sqlWalker)
        return '(' . $this->subselect->dispatch($sqlWalker) . ' LIMIT 1)';

It should be possible to extend this to make the LIMIT configurable - that exercise is left to the reader.

Learn More About PHP 7

If you'd like to learn more about PHP 7, check out my PHP 7 Upgrade Guide e-book. It covers everything you'll need to know about migrating to PHP 7, including:

  • New features
  • Changes & BC breaks
  • Deprecations
  • Removed functionality

It also provides instructions on how to install PHP 7 on your machine so you can start coding with it today!

You can read the free preview or grab a discounted copy here:

You can also check out other community resources including:

Happy coding!

About Colin O'Dell

Colin O'Dell

Colin O'Dell is a Lead Web Developer at Unleashed Technologies, a web and hosting firm based in Maryland. In addition to being an active member of the PHP League and maintainer of the league/commonmark project, Colin is also a PHP docs contributor, conference speaker, and author of the PHP 7 Migration Guide.