CODEWITHSUNDEEP
doctrine-ormsubquerylimitdql
MSánchez
MSánchez

Reputation: 545

doctrine2 DQL subquery 1 row limit

I want to do a DQL query like:

$dql = "select p
        from AcmeDemoBundle:UserTypeA p 
        where p.UserTypeB = :id
        and (
                 select top 1 r.boolean
                 from AcmeDemoBundle:Registry r
             ) 
        = true";

But it seems that TOP 1 it's not a valid function in doctrine2.

I can't figure out how can I limit the result of the subquery to one row.

Upvotes: 5

Views: 3846

Answers (2)

Colin O'Dell
Colin O'Dell

Reputation: 8647

Although Doctrine doesn't natively support this, you could implement a custom function named FIRST() to achieve this:

<?php

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)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->subselect = $parser->Subselect();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

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

(More details: https://www.colinodell.com/blog/201703/limiting-subqueries-doctrine-2-dql)

You should really only use this for read-only purposes since Doctrine will not include other related entities in the result (which may become orphaned or lost if you save it).

Upvotes: 1

Ocramius
Ocramius

Reputation: 25431

DQL does not support limits on subqueries and neither LIMIT nor OFFSET.

See http://www.doctrine-project.org/jira/browse/DDC-885

Upvotes: 3

Related Questions