Improved CASE expression in CakePHP 4.3

CakePHP 4.3 was recently released, and it contains a greatly improved way to build CASE expressions. CASE expressions are great when you want to do conditional aggregation or add computed fields to results. Lets say you wanted to create SQL that mapped integer values to string names, you could use SQL that looks like:

Show Plain Text
  2. CASE WHEN STATUS = 1 THEN 'complete'
  3. CASE WHEN STATUS = 0 THEN 'processing'
  4. ELSE 'unknown'
  5. END AS status_name,
  6. FROM orders

If you’ve not used the old CASE expression API in CakePHP, your SQL would have looked like the following:

Show Plain Text
  1. $query = $orders->find();
  2. $query->newExpr()->addCase(
  3.     // Conditions for the case
  4.     [
  5.         $query->newExpr()->add(['status' => 1]),
  6.         $query->newExpr()->add(['status' => 0]),
  7.     ],
  8.     // Values for the THEN clause. Because there are more
  9.     // values than conditions the last value becomes an ELSE.
  10.     [
  11.         'complete',
  12.         'processing',
  13.         'unknown',
  14.     ]
  15. );

Having the conditions and values linked by their offset in an array is a clunky API. It is hard to read and for all but the simplest case statements, the code is unreadable. While this design ‘works’ it is hard to use, easy to make mistakes with, has implicit behavior around how else is handled, and is basically unreadable. In 4.3 the same case expression is simpler to write and read:

Show Plain Text
  1. $query = $orders->find();
  2. $case = $query->newExpr()->case()
  3.     ->when(['status' => 1])->then('complete')
  4.     ->when(['status' => 0])->then('processing')
  5.     ->else('unknown');
  7. $query->select(['status_name' => $case]);

Much better. The new case API reuses keywords from the SQL representation. The else condition is easy to identify and the relationship between each condition and its outcome is instantly recognizable. Reusing SQL conventions helps make the new design easier to remember and operate. I’d like to give a big thank you to ndm2 and othercorey for their work on this.


Excellent improvement! I just finished upgrading a customer’s app from 4.2 to 4.3. Correcting the deprecations introduced in 4.3 has made the code more readable. I really appreciate the direction that you and the other devs are taking to advance the framework!

Charles Gehring on 11/24/21

Have your say: