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

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

Answer 2

I recommend you to not use this CASE syntax for solving this problem. It looks tricky.

Why don't you want to

$resultset = Doctrine_Query::create()
    ->select("t.code, t.description, t.id_outcome")
    ->from('LuOutcome t')
    ->orderBy('t.rank')
    ->fetchArray();

and then loop through $resultset and create this field (in_progress) manually depending on (id_outcome) value. You can use some small simple tiny method for that.

Benefits:

• simple
• readable

Answer 3

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();