问题:如何在 Doctrine 2 DQL 中使用 DATE()?

在 Symfony 中,当我在 mysql 中使用以下带有 DATE 函数的查询时出现错误

    SELECT employer.companyName AS employerName, jobs.jobId, jobs.industryId, 
jobs.focusId, jobs.companyName, jobs.employerId, jobs.jobTitle, DATE(jobs.createdDate) AS 
createdDate , DATE(jobs.endDate) AS endDate , jobs.replyTo, jobs.featured , jobs.jobType, 
jobs.status  FROM Acme\\AppsBundle\\Entity\\Jobs jobs , Acme\\AppsBundle\\Entity\\Employer 
employer  WHERE  jobs.employerId = employer.employerId  GROUP BY  jobs.jobId  ORDER BY 
jobs.jobId DESC 

为什么会这样以及有什么样的解决方法来克服这种情况,在数据库中,这些字段,即 end_date 被存储为 mysql type 'date'

    [2014-09-17 05:52:42] request.CRITICAL: Uncaught PHP Exception Doctrine\ORM\Query
\QueryException: "[Syntax Error] line 0, col 138: Error: Expected known function, got 
'DATE'" at /.../Doctrine/ORM/Query/QueryException.php line 52 {"exception":"[object] 
(Doctrine\\ORM\\Query\\QueryException: [Syntax Error] line 0, col 138: Error: Expected known function, got 'DATE' at /var/w.../doctrine/orm/lib/Doctrine/ORM/Query
/QueryException.php:52, Doctrine\\ORM\\Query\\QueryException: SELECT employer.companyName 
AS employerName, jobs.jobId, jobs.industryId, jobs.focusId, jobs.companyName, jobs.employerId, jobs.jobTitle, DATE(jobs.createdDate) AS createdDate , DATE(jobs.endDate) 
AS endDate , jobs.replyTo, jobs.featured , jobs.jobType, jobs.status  FROM Acme\\AppsBundle
\\Entity\\Jobs jobs , Acme\\AppsBundle\\Entity\\Employer employer  WHERE  jobs.employerId 
= employer.employerId  GROUP BY  jobs.jobId  ORDER BY  jobs.jobId DESC  at /var/w...
/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php:41)"} []

解答

DQL只知道几个标准的 sql 函数(例如coalesce)。为了能够使用您的自定义函数,您需要注册它并告诉doctrine如何将其转换为原始 sql。请遵循以下指南:

Symfony 文档

教义文档

并在这里查看我的答案