Search code examples
phpsymfonydoctrine-orm

Doctrine expected Lexer::T_FROM, got '>'


(Symfony 3.2.7)

I've got this query created inside repository:

$qb = $this->createQueryBuilder('c');
$qb->select($qb->expr()->gte('c.createdDate', 'CURRENT_TIMESTAMP()').' AS HIDDEN x');
$qb->orderBy('x');
$qb->getQuery()->execute();

It is generating this DQL:

SELECT c.createdDate >= CURRENT_TIMESTAMP() AS HIDDEN x 
FROM App\CatalogBundle\Entity\Company c 
ORDER BY x ASC

And also... it is generating this error:

[Syntax Error] line 0, col 21: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got '>'

What could be wrong here?


SOLUTION

Thanks to @bishop answer, I managed to create a solution on my problem with creating a custom Doctrine DQL function. Configuration:

doctrine:
    orm:
        dql:
            datetime_functions:
                DATES_COMPARE: AppBundle\DQL\DatesCompareFunction

And here is what mentioned class contains:

use Doctrine\ORM\Query;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;

class DatesCompareFunction extends FunctionNode
{
    /* @var Query\AST\Node  */
    public $param1;

    /* @var Query\AST\Node  */
    public $param2;

    /* @var Query\AST\Node  */
    public $param3;

    /** @inheritdoc */
    public function getSql(Query\SqlWalker $sqlWalker)
    {
        return sprintf(
            'CASE (%s %s %s) WHEN 1 THEN 1 ELSE 0 END',
            $this->param1->dispatch($sqlWalker),
            $this->param2,
            $this->param3->dispatch($sqlWalker)
        );
    }

    /** @inheritdoc */
    public function parse(Query\Parser $parser): void
    {
        $parser->match(Query\Lexer::T_IDENTIFIER);
        $parser->match(Query\Lexer::T_OPEN_PARENTHESIS);

        $this->param1 = $parser->StringPrimary();

        $parser->match(Query\Lexer::T_COMMA);

        $this->param2 = $parser->ComparisonOperator();

        $parser->match(Query\Lexer::T_COMMA);

        $this->param3 = $parser->StringPrimary();

        $parser->match(Query\Lexer::T_CLOSE_PARENTHESIS);
    }
}

So all is working properly for the following DQL:

SELECT DATES_COMPARE(c.subscriptionEndDate, >, CURRENT_TIMESTAMP()) AS x 
FROM App\CatalogBundle\Entity\Company c 
ORDER BY x DESC

Solution

  • Your statement is valid SQL, but remember: SQL != DQL. Checking the docs for DQL, we see the SELECT has this BNF:

    SelectExpression ::= (IdentificationVariable | ScalarExpression | AggregateExpression | FunctionDeclaration | PartialObjectExpression | "(" Subselect ")" | CaseExpression | NewObjectExpression) [["AS"] ["HIDDEN"] AliasResultVariable]

    But the >= operator appears only in ConditionalExpression:

    ConditionalExpression ::= ConditionalTerm {"OR" ConditionalTerm}* ConditionalTerm ::= ConditionalFactor {"AND" ConditionalFactor}* ConditionalFactor ::= ["NOT"] ConditionalPrimary ConditionalPrimary ::= SimpleConditionalExpression | "(" ConditionalExpression ")" SimpleConditionalExpression ::= ComparisonExpression | BetweenExpression | LikeExpression | InExpression | NullComparisonExpression | ExistsExpression | EmptyCollectionComparisonExpression | CollectionMemberExpression | InstanceOfExpression

    ComparisonExpression ::= ArithmeticExpression ComparisonOperator ( QuantifiedExpression | ArithmeticExpression )

    ComparisonOperator ::= "=" | "<" | "<=" | "<>" | ">" | ">=" | "!="

    Since SelectExpression does not include ConditionalExpression, you're getting a lexing error.

    To accomplish what you want, you'll need a custom function. See "Adding your own functions to the DQL language" for how to do this.