Symfony2 - Custom DQL Function Registered But Does Not work

Question

I am desperatelly trying to include the GREATEST function in mysql Symfony2, however, I still receive errors.

Added the function in DQL:

<?php

namespace DoctrineExtensions\Query\Mysql;

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

class Greatest extends FunctionNode
{
    private $field = null;

    private $values = [];

    /**
     * @param Parser $parser
     */
    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->field = $parser->ArithmeticExpression();
        $lexer = $parser->getLexer();

        while (count($this->values) < 1 ||
            $lexer->lookahead['type'] != Lexer::T_CLOSE_PARENTHESIS) {
            $parser->match(Lexer::T_COMMA);
            $this->values[] = $parser->ArithmeticExpression();
        }

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

    /**
     * @param SqlWalker $sqlWalker
     * @return string
     */
    public function getSql(SqlWalker $sqlWalker)
    {
        $query = 'GREATEST(';

        $query .= $this->field->dispatch($sqlWalker);

        $query .= ', ';

        for ($i = 0; $i < count($this->values); $i++) {
            if ($i > 0) {
                $query .= ', ';
            }

            $query .= $this->values[$i]->dispatch($sqlWalker);
        }

        $query .= ')';

        return $query;
    }
}

In Config.yml

orm:
        auto_generate_proxy_classes: false
        auto_mapping: true
        dql:
            datetime_functions:
                Greatest: DoctrineExtensions\Query\Mysql\Greatest

Problem: When executing the following code block in my Repository, the following errors occur:

$admitObj = $em->createQueryBuilder();
      $admitObj
        ->select('A')
        ->from("EntityBundle:Admit", "A")
        ->orderBy("GREATEST( COALESCE(A.date1, 0), COALESCE(A.date2, 0))", "DESC");

      $admit = $admitObj->setMaxResults(1)->getQuery()->getResult();

ERROR: [Syntax Error] line 0, col 111: Error: Expected end of string, got '('

What am I missing? Why isn't DQL/Symfony/PDO/... recognizing the function? Any help is highly appreciated!

Answer 1

After long research I find that it was problem in my parser.php file. I solved this issue to replace below code in my parser file. It is issue of using greatest function in order by.

/**
     * OrderByClause ::= "ORDER" "BY" OrderByItem {"," OrderByItem}*
     *
     * @return \Doctrine\ORM\Query\AST\OrderByClause
     */
    public function OrderByClause()
    {
        $this->match(Lexer::T_ORDER);
        $this->match(Lexer::T_BY);

        $orderByItems = array();
        $orderByItems[] = $this->OrderByItem();

        while ($this->lexer->isNextToken(Lexer::T_COMMA)) {
            $this->match(Lexer::T_COMMA);

            $orderByItems[] = $this->OrderByItem();
        }

        return new AST\OrderByClause($orderByItems);
    }