CODEWITHSUNDEEP
cakephpcakephp-4.x
Abey
Abey

Reputation: 1428

CakePHP doing auto lower casing in Query

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?

Upvotes: 0

Views: 132

Answers (1)

ndm
ndm

Reputation: 60463

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

Upvotes: 2

Related Questions