IFNULL in Symfony2 Doctrine query builder

Question

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?

Attorney

I have the same problem on my symfony2 project – Choe

Answer 1

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...

Attorney

Link does not exist – Educatory

Answer 2

This is the valid link with the DQL Extension

Edit with the solution explained:

1. Create the following directory under your project src path: /src/DoctrineExtensions/Query/Mysql

2. Put there the DQL Extension file (IfNull.php in this case)

3. Edit your src/config/packages/doctrine.yaml and insert this new lines:

doctrine:
    ...
    orm:
        ...
        dql:
            numeric_functions:
                IFNULL: App\DoctrineExtensions\Query\Mysql\IfNull

4. In your entity repository you can call this function like this:

$qb = $this->createQueryBuilder('tl')
           ->andWhere('IFNULL(tl.app,0) = 1');

Vincennes

Answer 3

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://mcmap.net/q/821895/-ifnull-in-symfony2-doctrine-query-builder.

Note: COALESCE might behave slightly different than IFNULL depending on your database. https://mcmap.net/q/225065/-what-is-the-difference-between-ifnull-and-coalesce-in-mysql 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

Skiagraph