Simple IF Testing Report in Doctrine
Does Doctrine support IF statements? I am getting the following error:
Expected known function, got 'IF'
when executing this query with IF:
$qb->select("c.id, IF(c.type_id LIKE 9, c.name, c.lastname) as name")
It works great when rewriting in pure SQL. Any workarounds?
Yes if
statements in doctrine are not supported, you can convert it tocase 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, the function is concat
resolved incase-when
The answer is yes is very legal. Here's 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)
Note: for Doctrine 2
Consider using some additional functionality in such cases (without trying to "work around" them). For example, a great solution adding almost all the necessary (not supported out of the box) materials for Doctrine 2
is DoctrineExtensions from beberlei (github). With it one can use IF
-statement directly , as in the OP's case:
("Symfony Example") For example, in your config.xml
add the lines:
orm:
..
entity_managers:
....
dql:
....
string_functions:
IF: DoctrineExtensions\Query\Mysql\IfElse
Then you can use it anywhere like:
$qb->select("..IF(condition, true-state, false-state)...")