与Doctrine和Symfony2一起使用自定义DQL函数时出错

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 :

我使用Symfony 2和ORM Doctrine。我想创建并注册自定义DQL函数。实际上，我想在我的请求中使用SQL函数“CAST”，如下所示：

    $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" :

为此，我创建了一个扩展“FunctionNode”的“CastFunction”：

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 :

当然，我在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."

“[语义错误]第0行，第83行附近'UNSIGNED）'：错误：未定义'UNSIGNED'。”

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

我搜索但我不知道问题在哪里！

Have you got a idea?

你有个主意吗？

2 个解决方案

#1

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).

经过几次搜索，我终于找到了解决方案。我有两个问题：首先我的解析函数是错误的，第二，我在我的订单中调用了一个SQL函数（谢谢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:

现在，我可以在我的存储库中完美地使用SQL函数'CAST'：

$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

最好的祝福

#2

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.

找不到引用，但order by子句中不允许使用函数。您需要在select语句中转换值，然后按它排序。

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.

假设你的CAST函数写得正确。

#1