Cakephp 3.0 - Query builder for case statements

Question

I follow the cookbook of cakephp 3 to create a case statement http://book.cakephp.org/3.0/en/orm/query-builder.html#case-statements

but the result sql query is not as expected. The 'Else' part of the statement is missing

This is my code:

$query = $this->Attendees->find()->contain(['Users']);
$lastModifedCase = $query->newExpr()->addCase($query->newExpr()->add(['Attendees.modified <' => 'Users.modified']), [ 'Users.modified', 'Attendees.modified'], 'datetime');
$query->select(['Attendees.id', 'lastModified' => $lastModifedCase, 'Users.firstName', 'Users.lastName']);

die(var_dump($query->__debugInfo()));

In the debugInfo, this is the sql that I can see:

array(13) {
  ["sql"]=>
  string(265) "SELECT Attendees.id AS `Attendees__id`, (CASE WHEN Attendees.modified < :c0 THEN :c1 END) AS `lastModified`, Users.firstName AS `Users__firstName`, Users.lastName AS `Users__lastName` FROM attendees Attendees INNER JOIN users Users ON Users.id = (Attendees.user_id)"
  ["params"]=>
  array(2) {
    [":c0"]=>
    array(3) {
      ["value"]=>
      string(14) "Users.modified"
      ["type"]=>
      string(8) "datetime"
      ["placeholder"]=>
      string(2) "c0"
    }
    [":c1"]=>
    array(3) {
      ["value"]=>
      string(14) "Users.modified"
      ["type"]=>
      string(8) "datetime"
      ["placeholder"]=>
      string(2) "c1"
    }
  }
  ["defaultTypes"]=>
  array(8) {
    ["Attendees.id"]=>
    string(7) "integer"
    ["id"]=>
    string(7) "integer"
    ["Attendees.user_id"]=>
    string(7) "integer"
    ["user_id"]=>
    string(7) "integer"
    ["Attendees.created"]=>
    string(8) "datetime"
    ["created"]=>
    string(8) "datetime"
    ["Attendees.modified"]=>
    string(8) "datetime"
    ["modified"]=>
    string(8) "datetime"
  }

The "funny" thing is that the example in the cookbook contains an error (variable $unpublishedCase should be $notPublishedCase) and also doesn't give me the expected result. This is its debugInfo (ps: I only replaced "$this->article" by my attendees table for testing the example)

array(13) {
  ["sql"]=>
  string(190) "SELECT (SUM(CASE WHEN published = :c0 THEN :c1 END)) AS `number_published`, (SUM(CASE WHEN published = :c2 THEN :c3 END)) AS `number_unpublished` FROM attendees Attendees GROUP BY published "
  ["params"]=>
  array(4) {
    [":c0"]=>
    array(3) {
      ["value"]=>
      string(1) "Y"
      ["type"]=>
      NULL
      ["placeholder"]=>
      string(2) "c0"
    }
    [":c1"]=>
    array(3) {
      ["value"]=>
      int(1)
      ["type"]=>
      string(7) "integer"
      ["placeholder"]=>
      string(2) "c1"
    }
    [":c2"]=>
    array(3) {
      ["value"]=>
      string(1) "N"
      ["type"]=>
      NULL
      ["placeholder"]=>
      string(2) "c2"
    }
    [":c3"]=>
    array(3) {
      ["value"]=>
      int(1)
      ["type"]=>
      string(7) "integer"
      ["placeholder"]=>
      string(2) "c3"
    }
  }
  ["defaultTypes"]=>
  array(8) {
    ["Attendees.id"]=>
    string(7) "integer"
    ["id"]=>
    string(7) "integer"
    ["Attendees.user_id"]=>
    string(7) "integer"
    ["user_id"]=>
    string(7) "integer"
    ["Attendees.created"]=>
    string(8) "datetime"
    ["created"]=>
    string(8) "datetime"
    ["Attendees.modified"]=>
    string(8) "datetime"
    ["modified"]=>
    string(8) "datetime"
  }

When I dig into the cakephp code, I think my problem comes from the constructor of Cake\Database\Expression\CaseExpression:

public function __construct($conditions = [], $values = [], $types = [])
    {

        if (!empty($conditions)) {
            $this->add($conditions, $values, $types);
        }

        if (is_array($conditions) && is_array($values) && count($values) > count($conditions)) {
            end($values);
            $key = key($values);
            $this->elseValue($values[$key], isset($types[$key]) ? $types[$key] : null);
        }
    }

$conditions is not considered as an array, so the else value is not set.

The version of cakephp is 3.0.1

The version of php is 5.4.34

What do you guys think? Am I doing something wrong or is it a bug?

Thanks in advance for your enlightments

Answer 1

When you use the array syntax for generating conditions you need to keep in mind that the value par of the array will always be treated as a param and not a raw string:

['field_name >' => 'this is just a value, not a raw string']

The reason is that it prevents from SQL injection attacks. TO fix your problem do like this:

['Attendees.modified < Users.modified']

Instead of this:

['Attendees.modified <' => 'Users.modified']

Answer 2

I found my mistake after a good night, I share it with you ... hope it will help someone someday.

The syntax was not correct, so $condition was definitely not an array.

$lastModifedCase = $query->newExpr()->addCase(
    $query->newExpr()->add([
        'Attendees.modified <' => 'Users.modified'
    ]),
    ['Users.modified', 'Attendees.modified'],
    'datetime'
);

The correct syntax is:

$lastModifedCase = $query->newExpr()->addCase(
    [$query->newExpr()->add('Attendees.modified < Users.modified')], 
    [ 'Users.modified', 'Attendees.modified'], 
    ['datetime','datetime']
);

I also:

• adjusted the conditions based on the answer of José Lorenzo
• specified the type for the each value (an array of 'datetime' instead of a single string). Otherwise, I got a "wrong type" error.