SQL Functions
In SQL you have plenty functions which help you to improve your work. For example this is some usefull functions :
- SUM() Calculate the sum of the result
- MAX() Get the maximum result
- MIN() Get the minimum result
- COUNT() Count the number of row of the result
And fortunatly we can use them in DQL... but unfortunatly not all of them !
DQL
When I code with Symfony and I need to query my Database, I always use DQL. I love that thing, I can customize it and write PHP to be more specific.
But few days ago, I needed to display only the date (not the time) from my UserRepository. In SQL you do this => SELECT DATE_FORMAT(date, format) WHERE ...
, so I started to write my code in DQL :
public function findByCreatingDate(User $userId)
{
return $this->createQueryBuilder('u')
->select('DATE_FORMAT(u.createdAT, "%d-%m-%Y)')
->andWhere('u.id = :userId')
->setParameter('userId', $userId)
->getQuery()
->getResult()
;
}
I refreshed the page and the scariest red smilling ghost appeared and told me "Error: Expected known function, got 'DATE'"
This is one of some functions that DQL does not support. Well I had 3 choices :
- Type in SQL
- Write my own DQL function
- Google the error !
The first choice has been removed because either I write all in SQL or in DQL, I never mix and I did'nt want to rewrite all my queries.
I Google my error before doing the second choice 🙄 and I found exactly what I was looking for in this Github.
I just needed to do composer require beberlei/doctrineextensions
then in my doctrine.yaml under orm :
dql:
datetime_functions:
date_format: DoctrineExtensions\Query\Mysql\DateFormat
Refresh the page and goodbye terrible red ghost 👻
Conclusion
I hope it's gonna be usefull to you as it has been to me
Cheers
Top comments (0)