Get your SQL functions in DQL

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 :

1. Type in SQL
2. Write my own DQL function
3. 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