在带有 DQL 的 WHERE 子句中使用 DATE() 函数 [英] Use a DATE() function in a WHERE clause with DQL

问题描述

我在执行此 DQL 查询时遇到一个奇怪的错误:

I get a strange error when I execute this DQL query:

SELECT u FROM User u LEFT JOIN u.schedule s WHERE DATE(s.timestamp) = DATE(NOW())

Doctrine 抛出异常并显示消息:

The exception is thrown by Doctrine with the message:

Expected known function, got 'DATE'

这个问题看起来类似于这个错误，但解决了 DATE() 函数一个 GROUP BY 子句，该错误已针对 Doctrine 2.2 关闭.此刻，我得到了Doctor 2.4-DEV的异常.

The problem looks similar to this bug, but that addresses the DATE() function in a GROUP BY clause and the bug is closed for Doctrine 2.2. At this moment, I get the exception with doctrine 2.4-DEV.

该查询旨在选择今天安排的所有用户.有什么办法可以创建这个 DQL?我在 phpMyAdmin 中测试了 SQL 版本，查询没有引发错误.可能有什么问题?

The query is meant to select all users scheduled for today. Is there any way I can create this DQL? I tested the SQL version in phpMyAdmin and there the query does not raise an error. What might be wrong?

推荐答案

您可以通过使用 自定义函数:

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

class DateFunction extends FunctionNode
{
    private $arg;

    public function getSql(SqlWalker $sqlWalker)
    {
        return sprintf('DATE(%s)', $this->arg->dispatch($sqlWalker));
    }

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->arg = $parser->ArithmeticPrimary();

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

然后在你的代码中注册这个函数:

Then registering this function in your code:

$em->getConfiguration()->addCustomDatetimeFunction('DATE', 'DateFunction');

您的 DQL 查询将起作用！

And your DQL query will work!

这篇关于在带有 DQL 的 WHERE 子句中使用 DATE() 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！