How is the IFNULL of SQL implemented in Symfony2 Doctrine Query Builder?
Let's say I have this query:
select * from ticket order by IFNULL(modified_date, '2000-01-01') DESC, created_date DESC
I have this DQL:
$this->qb->select("t, c.name")
->from("Ticket", "t");
$this->qb->orderBy("t.modifiedDate", "DESC");
$this->qb->addOrderBy("t.createdDate", "DESC");
Now how to add the IFNULL part?
Ok, done some research and found that there is no such implementation.
Googled a little more, and got that this kind of missing features can be added to Doctrine as own functions.
Found this extension on GitHub I think this will work. But wonder if ther would be any problems or conflicts with Doctrine versions...
This is the valid link with the DQL Extension
Edit with the solution explained:
Create the following directory under your project src path: /src/DoctrineExtensions/Query/Mysql
Put there the DQL Extension file (IfNull.php in this case)
Edit your src/config/packages/doctrine.yaml and insert this new lines:
doctrine:
...
orm:
...
dql:
numeric_functions:
IFNULL: App\DoctrineExtensions\Query\Mysql\IfNull
$qb = $this->createQueryBuilder('tl')
->andWhere('IFNULL(tl.app,0) = 1');
Depending on your usecase you may be able to use the builtin "COALESCE" expression instead of installing the "IFNULL" extension.
The usage then is basically the same as with the IFNULL expression. Just replace IFNULL with COALESCE in the example in https://stackoverflow.com/a/68827681/1707003.
Note: COALESCE might behave slightly different than IFNULL depending on your database. https://stackoverflow.com/a/18528590/1707003 contains some great explanations.
List of builtin case-expressions: https://www.doctrine-project.org/projects/doctrine-orm/en/2.13/reference/dql-doctrine-query-language.html#case-expressions
