Using a 'case when' in a Doctrine select statement

Question

I have a select query I'd like to perform with Doctrine:

 $resultset = Doctrine_Query::create()
    ->select("t.code, t.description, case when t.id_outcome = 1 then 1 else 0 end as in_progress")
    ->from('LuOutcome t')
    ->orderBy('t.rank')
    ->fetchArray();

And it barfs on the 'case'. The documentation does not mention that it's possible (or not).

I'm wondering if Doctrine lacks the capacity to do so. If so, it's a rather major omission. Does anyone know of a work-around?

Answer 1

I just had the same problem and, at first glance, appear to have a workaround. I believe you can 'fool' the Doctrine Select parser into treating it as a sub-query by wrapping it in parenthesis.

Give this a try:

$resultset = Doctrine_Query::create()
->select("t.code, t.description, (case when t.id_outcome = 1 then 1 else 0 end) as in_progress")
->from('LuOutcome t')
->orderBy('t.rank')
->fetchArray();

Answer 2

Case statements do appear to have been added to doctrine at some point: https://github.com/doctrine/orm-documentation/commit/189c729f15d2fafecf92662cad9553c2ec3dccd7#diff-0

Answer 3

The BNF grammar for the Doctrine Query Language doesn't seem to contain anything related to a CASE construct.

Answer 4

As mentioned by adavea, Doctrine now has added support for CASE expressions. You can do something like

 $resultset = Doctrine_Query::create()
->select("t.code, t.description")
->addSelect("CASE WHEN(t.id_outcome = 1) THEN 1 ELSE 0 END as in_progress")
->from('LuOutcome t')
->orderBy('t.rank')
->fetchArray();

Hope this might help someone, thanks!