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

问题描述

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

 SELECT 雇主.companyName AS 雇主名称，jobs.jobId，jobs.industryId，jobs.focusId、jobs.companyName、jobs.employerId、jobs.jobTitle、DATE(jobs.createdDate) AScreatedDate ， 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 =雇主.employerId GROUP BY jobs.jobId ORDER BY作业.jobId DESC

这是为什么以及有什么解决方法可以克服这种情况，在数据库中这些字段即 end_date 存储为 mysql 类型日期"

 [2014-09-17 05:52:42] request.CRITICAL: Uncaught PHP Exception DoctrineORMQueryQueryException: "[语法错误] 第 0 行，第 138 列:错误:预期已知函数，得到'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/询问/QueryException.php:52, Doctrine\ORM\Query\QueryException: SELECT雇主.companyNameAS 雇主名称、jobs.jobId、jobs.industryId、jobs.focusId、jobs.companyName、jobs.employerId、jobTitle、DATE(jobs.createdDate) AS createdDate、DATE(jobs.endDate)AS endDate、jobs.replyTo、jobs.featured、jobType、jobs.status FROM Acme\AppsBundle\Entity\Jobs 工作 , Acme\AppsBundle\Entity\Employer 雇主 WHERE jobs.employerId=雇主.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 文档

学说文件

并在此处查看我的答案>

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 DoctrineORMQuery
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屋！