CakePHP count() return wrong results?

Question

CakePHP 3.6.14

This code reproduce wrong number:

        $where = [
            'Postings.source' => $source,
            'Postings.approved' => 1,
            'Postings.deleted' => 0,
            'Postings.disabled' => 0
        ];

        if ($source !== null) {
            $where['Postings.created >='] = '(NOW() - INTERVAL 3 DAY)';
        }
        $count = $this->Postings
            ->find()
            ->where($where)
            ->count();
        debug($count); exit;

       // 77568 total of all records
########## DEBUG ##########
[
    'Postings.source' => 'xzy',
    'Postings.approved' => (int) 1,
    'Postings.deleted' => (int) 0,
    'Postings.disabled' => (int) 0,
    'Postings.created >=' => '(NOW() - INTERVAL 3 DAY)'
]

//SQL produced by this query:

SELECT (COUNT(*)) AS `count` 
FROM postings Postings 
WHERE (
   Postings.source = 'xzy' 
   AND Postings.approved = 1 
   AND Postings.deleted = 0 
   AND Postings.disabled = 0 
   AND Postings.created >= '(NOW() - INTERVAL 3 DAY)' // <<<< with quotes
)

but raw sql query:

SELECT COUNT(*) as `count
FROM `postings` 
WHERE `source` = 'xzy' 
AND `approved` = 1 
AND `deleted` = 0 
AND `disabled` = 0 
AND `created` >= (NOW() - INTERVAL 3 DAY) // <<< without quotes
// return correct num 2119

How to fix?

ndm ndm · Accepted Answer · 2019-01-14T22:51:00

Values on the right hand side of a key => value condition are always subject to binding/casting/escaping, unless it's an expression object. Look at the generated query, your SQL snippet will end up as a string literal, ie:

created >= '(NOW() - INTERVAL 3 DAY)'

Long story short, use an expression, either a raw one:

$where['Postings.created >='] = $this->Postings->query()->newExpr('NOW() - INTERVAL 3 DAY');

or use the functions builder:

$builder = $this->Postings->query()->func();
$where['Postings.created >='] = $builder->dateAdd($builder->now(), -3, 'DAY');

CakePHP count() return wrong results?

2 Answers