如何在Doctrine 2 DQL中使用DATE（）？ [英] How can I use DATE() in Doctrine 2 DQL?

问题描述

在Symfony中，当我在mysql中使用DATE函数的以下查询时，我收到错误

  SELECT employer.companyName AS雇主名称，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 
雇主WHERE jobs.employerId = employer.employerId GROUP BY jobs.jobId ORDER BY 
 jobs.jobId DESC 
  

为什么要解决这个问题，而且在数据库中这些字段即end_date存储为mysql类型'date'

  [2014-09-17 05:52:42] request.CRITICAL：未捕获PHP异常Doctrine\ORM\Query 
 \\ \\QueryExcep [Syntax Error] line 0，col 138：Error：Expected known function，got 
'DATE'at /.../Doctrine/ORM/Query/QueryException.php line 52 {exception： [object] 
（Doctrine\\MYM\\\QueryQuery\\\QueryException：[Syntax Error] line 0，col 138：Error：预期的已知函数，在/ var / w ... / doctrine / orm / lib / Doctrine / ORM / Query 
 /QueryException.php:52，Doctrine\\\\MM\\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 \\雇主雇主WHERE jobs.employerId 
 = employer.employerId GROUP BY jobs.jobId ORDER BY jobs.jobId DE SC在/ var / w ... 
 /doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php:41）} [] 
  

解决方案

DQL 只知道几个标准的sql函数（<$例如c $ c> coalesce ）。为了能够使用您的自定义函数，您需要注册它，并告诉 doctrine 如何将其转换为raw sql。请按照以下指南：

Symfony Doc

Doctrine Doc

并检查我的答案其中

IN Symfony, when i used the following query with DATE function in the mysql i get a error

    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 

Why is this and what sort of workaround is there to overcome this situation, in the Database these fields i.e end_date is are stored as 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 is only aware of few standard sql functions (coalesce for example). To be able to use your custom function you need to register it and tell doctrine how to translate it into raw sql. Follow these guides:

Symfony Doc

Doctrine Doc

And check my answer here

这篇关于如何在Doctrine 2 DQL中使用DATE（）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！