Clever Geek Handbook

Cakephp 3.0 - query builder for case statements

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

but the result of sql query is not as expected. Invalid part of the "Else" statement

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 debugInfo this is the sql I 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 cookbook example contains an error ($ unpublishedCase must be $ notPublishedCase) and also does not produce the expected result. This is his debugInfo (ps: I just replaced "$ this-> article" with my visitors 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 step into the cakephp code, I think my problem comes from the Cake \ Database \ Expression \ CaseExpression constructor:

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

The $ condition is not treated as an array, so the else value is not set.

Cakephp version 3.0.1

Php version is 5.4.34

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

Thanks in advance for your clarification

+3


source to share


2 answers


I found my mistake after a good night, I shared it with you ... hope it helps someone someday.

The syntax is incorrect, so the $ condition is 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 terms based on Jose Lorenzo's answer
  • specifies the type for each value (instead of a single string 'datetime' array). Otherwise, I got a "wrong type" error.
+1


source


When you use the array syntax to generate conditions, you need to keep in mind that the par value of the array will always be treated as a parameter, not a raw string:

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

The reason is that it prevents SQL injection attacks. To fix your problem, follow these steps:



['Attendees.modified < Users.modified']

Instead of this:

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


source






More articles:


All Articles