CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-ormdoctrine
mahen3d
mahen3d

Reputation: 7774

How can I use DATE() in Doctrine 2 DQL?

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)"} []

Upvotes: 14

Views: 28910

Answers (3)

Dima Medvedchuk
Dima Medvedchuk

Reputation: 121

Or if you don't want add a new dependency, you can use code like this:

$dateTime = new \DateTime();
$qb
   ->andWhere('jobs.endDate BETWEEN :dateMin AND :dateMax')
   ->setParameters(
       [
            'dateMin' => $dateTime->format('Y-m-d 00:00:00'),
            'dateMax' => $dateTime->format('Y-m-d 23:59:59'),
       ]
    );

Upvotes: 12

qooplmao
qooplmao

Reputation: 17759

In addition to the accepted answer there are a tonne of pre-built custom functions available at https://github.com/beberlei/DoctrineExtensions .

These can be then registered in your config like

doctrine:
    orm:
        dql:
            string_functions:
                DATE: DoctrineExtensions\Query\Mysql\Date

and can then be used in your DQL (as in your query) like

DATE(jobs.endDate) AS endDate

Upvotes: 27

Tomasz Madeyski
Tomasz Madeyski

Reputation: 10910

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

Upvotes: 8

Related Questions