Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Error when use custom DQL function with Doctrine and Symfony2

I use Symfony 2 and the ORM Doctrine. I want to create and register a custom DQL function. In fact, I want to use the SQL function "CAST" in my request, like this :

    $qb = $this->_em->createQueryBuilder();
    $qb->select('d')
       ->from('\Test\MyBundle\Entity\MyEntity', 'd')
       ->orderBy('CAST(d.myField AS UNSIGNED)', 'ASC')

    return $qb->getQuery()->getResult();

For this, I have created a "CastFunction" who extend "FunctionNode" :

namespace Test\MyBundle\DQL;

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

class CastFunction extends FunctionNode
{
    public $firstDateExpression = null;
    public $secondDateExpression = null;

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->firstDateExpression = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_IDENTIFIER);
        $this->secondDateExpression = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return sprintf('CAST(%s AS %s)', $this->firstDateExpression->dispatch($sqlWalker), $this->secondDateExpression->dispatch($sqlWalker));
    }
}

Of course, I have registered this class in my config.yml :

doctrine:
    orm:
        dql:
            string_functions:
                CAST: Test\MyBundle\DQL\CastFunction

Now, when I try my request, I obtain the following error:

"[Semantical Error] line 0, col 83 near 'UNSIGNED)': Error: 'UNSIGNED' is not defined."

I search but I don't where is the problem!

Have you got a idea?

like image 355
Scipius2012 Avatar asked Sep 30 '13 14:09

Scipius2012


2 Answers

After several search, I have finally found the solution. I had two problems: first my parse function was wrong, second, I called a SQL function in my orderBy (thank you Cerad).

So, here is my correct class:

namespace Ypok\YPoliceBundle\DQL;

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

class CastFunction extends FunctionNode
{
    public $firstDateExpression = null;
    public $unit = null;    

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->firstDateExpression = $parser->StringPrimary();

        $parser->match(Lexer::T_AS);

        $parser->match(Lexer::T_IDENTIFIER);
        $lexer = $parser->getLexer();
        $this->unit = $lexer->token['value'];

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

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return sprintf('CAST(%s AS %s)',  $this->firstDateExpression->dispatch($sqlWalker), $this->unit);
    }
}

And now, I can use perfectly the SQL function 'CAST' in my repository:

$qb = $this->_em->createQueryBuilder();
$qb->select('d, CAST(d.myField AS UNSIGNED) AS sortx')
   ->from('\Test\MyBundle\Entity\MyEntity', 'd')
   ->orderBy('sortx', 'ASC')

return $qb->getQuery()->getResult();

Best regards

like image 127
Scipius2012 Avatar answered Nov 15 '22 14:11

Scipius2012


Can't find the reference but functions are not allowed in the order by clause. You need to cast your value in the select statement then sort by it.

Something like:

$qb->select('d, CAST(d.myField AS UNSIGNED) AS sortx)
   ->from('\Test\MyBundle\Entity\MyEntity', 'd')
   ->orderBy('sortx, 'ASC')

That is assuming your CAST function is written correctly.

like image 33
Cerad Avatar answered Nov 15 '22 13:11

Cerad