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

2022-01-16 00:00:00 php mysql symfony doctrine doctrine-orm

我使用 Symfony 2 和 ORM 原则.我想创建和注册一个自定义 DQL 函数.其实我想用SQL函数CAST"我的要求，像这样:

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('TestMyBundleEntityMyEntity', 'd')
       ->orderBy('CAST(d.myField AS UNSIGNED)', 'ASC')

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

为此，我创建了一个扩展FunctionNode"的CastFunction":

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

namespace TestMyBundleDQL;

use DoctrineORMQueryASTFunctionsFunctionNode;
use DoctrineORMQueryLexer;
use DoctrineORMQuerySqlWalker;
use DoctrineORMQueryParser;

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

    public function parse(DoctrineORMQueryParser $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(DoctrineORMQuerySqlWalker $sqlWalker)
    {
        return sprintf('CAST(%s AS %s)', $this->firstDateExpression->dispatch($sqlWalker), $this->secondDateExpression->dispatch($sqlWalker));
    }
}

当然，我已经在我的 config.yml 中注册了这个类:

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

doctrine:
    orm:
        dql:
            string_functions:
                CAST: TestMyBundleDQLCastFunction

现在，当我尝试我的请求时，我收到以下错误:

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

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

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

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

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

你有想法吗?

推荐答案

经过多次搜索，终于找到了解决方案.我有两个问题:首先我的解析函数错误，其次，我在 orderBy 中调用了一个 SQL 函数(谢谢 Cerad).

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 YpokYPoliceBundleDQL;

use DoctrineORMQueryASTFunctionsFunctionNode;
use DoctrineORMQueryLexer;
use DoctrineORMQuerySqlWalker;
use DoctrineORMQueryParser;

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

    public function parse(DoctrineORMQueryParser $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(DoctrineORMQuerySqlWalker $sqlWalker)
    {
        return sprintf('CAST(%s AS %s)',  $this->firstDateExpression->dispatch($sqlWalker), $this->unit);
    }
}

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

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('TestMyBundleEntityMyEntity', 'd')
   ->orderBy('sortx', 'ASC')

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

最好的问候

相关文章