DEV Community

Cover image for Get your SQL functions in DQL
Mickaël
Mickaël

Posted on

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()
        ;
    }
Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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

Discussion (0)