Simple IF test statement in Doctrine

Question

Does Doctrine support IF statements? I get the following error:

Expected known function, got 'IF'

while executing this query with IF:

$qb->select("c.id, IF(c.type_id LIKE 9, c.name, c.lastname) as name")

It works fine while re-written in pure SQL. Any workarounds?

Answer 1

Yes if statements in doctrine is not supported you may convert it to case when

IF(c.type_id LIKE 9, c.name, c.lastname) as name

to

case when c.type_id = 9 then c.name else c.lastname end as name

UPDATE: From the comment does concat function is allowed in case-when

The answer is yes very much allowed. Here is an example

mysql> select * from timesheets ;
+-----------+-------+----------+
| client_id | hours | category |
+-----------+-------+----------+
|         1 |  1.50 | onsite   |
|         1 |  1.50 | onsite   |
|         1 |  1.00 | remote   |
|         2 |  1.50 | remote   |
+-----------+-------+----------+
4 rows in set (0.00 sec)

mysql> select 
case when category = 'onsite' then concat('ON',' ',hours) else hours
end as dd from timesheets ;
+---------+
| dd      |
+---------+
| ON 1.50 |
| ON 1.50 |
| 1.00    |
| 1.50    |
+---------+
4 rows in set (0.00 sec)

Answer 2

Note: for Doctrine 2

Think it's better to use some extra functional in such cases ( without trying "to circumvent" theirs). E.g. an excellent solution adding almost all necessary ( not supported from box ) stuff for Doctrine 2 is DoctrineExtensions by beberlei (github). With it it's possible to use directly IF-statement like in OP's case:

("Symfony-example") E.g. in your config.xml add lines:

orm:
    ..
    entity_managers:
            ....
            dql:
                ....
                string_functions:
                    IF: DoctrineExtensions\Query\Mysql\IfElse

Then U can use it anywhere like:

 $qb->select("..IF(condition, true-state, false-state)...")