[FIXED] CakePHP doing auto lower casing in Query

Issue

Below is a part of my query which filters based on like operator in a specific format

$baseQuery->where(
     ["DATE_FORMAT(`CallRequests`.`updated_date`, '%m/%d/%Y %l:%i %p') like" => $string . '%'],
     ['updated_date' => 'string']
);

But for some reason CakePHP is auto-lowercasing %Y before query execution.

Below is the related part in the query from the debug dump:

DATE_FORMAT(`CallRequests`.`created_date`, '%m/%d/%y %l:%i %p') like :c0'

I am aware that this can be avoided using raw queries.

But is there a workaround for this behaviour without using raw queries?

Solution

You're not supposed to put SQL snippets in the key of a key => value condition. The key side is ment to hold an identifier, and optionally an operator, separated by whitespace. When processed, the right hand side of the whitespace, ie the operator part, is being lowercased, hence what you're experiencing.

You're already using raw SQL, so it's not a big leap to going a step further and use a single value condition to provide a complete raw SQL snippet, and use bindings to inject your value:

$baseQuery
    ->where([
        "DATE_FORMAT(`CallRequests`.`updated_date`, '%m/%d/%Y %l:%i %p') like :date",
    ])
    ->bind(':date', $string . '%', 'string');

Alternatively you can use expressions, both for the DATE_FORMAT() function and the LIKE comparison:

$baseQuery->where(
    function (
        \Cake\Database\Expression\QueryExpression $exp,
        \Cake\ORM\Query $query
    ) use ($string) {
        $dateFormat = $query->func()->DATE_FORMAT([
            $query->identifier('CallRequests.updated_date'),
            '%m/%d/%Y %l:%i %p'
        ]);

        return [
            $exp->like($dateFormat, $string . '%')
        ];
    }
);

See also

• Cookbook > Database Access & ORM > Query Builder > Binding Values
• Cookbook > Database Access & ORM > Query Builder > Using SQL Functions
• Cookbook > Database Access & ORM > Query Builder > Advanced Conditions

Answered By - ndm