[FIXED] CakePHP 3: Empty result set returned while database has rows for same query

Issue

I am working on limiting number of rows returned by a specific query to ones that have been modified within last 30 days using column modified. The code for this selection goes like this:

            $conditions = ['Models.receiver'   => $Model->receiver,
                           'Models.status IN'  => [40, 50],
                           //'Models.modified >' => 'NOW() - INTERVAL 30 DAY'
            ];
            $options = [
                'limit'      => 1,
                'conditions' => $conditions,
                'order'      => ['Models.modified' => 'DESC']
            ];
            $result = $this->Models->find('all', $options)->first();
            debug($this->Models->find('all', $options));

The raw query when executed on MySQL command line prompt, without limitation by date on column modified, gives me following output:

SELECT
     receiver,
     status,
     modified
FROM
    printouts
WHERE
    receiver = (INT) X AND
    status IN (40, 50)
ORDER BY
    modified DESC LIMIT 1;

+----------+--------+---------------------+
| receiver | status | modified            |
+----------+--------+---------------------+
|  (INT) X |     50 | 2016-11-21 05:33:02 |
+----------+--------+---------------------+
1 row in set (0.00 sec)

The corresponding result set received in CakePHP 3 Controller is also correct:

object(App\Model\Entity\Model) {

    'receiver' => (int) X,
    'status' => (int) 50,
    'modified' => object(Cake\I18n\Time) {

            'time' => '2016-11-21T05:33:02+0000',
            'timezone' => 'UTC',
            'fixedNowTime' => false

    },
    '[new]' => false,
    '[accessible]' => [
            //... removed
    ],
    '[dirty]' => [],
    '[original]' => [],
    '[virtual]' => [],
    '[errors]' => [],
    '[repository]' => 'Models'

}

The query received from debugger is mentioned here:

object(Cake\ORM\Query) {

    '(help)' => 'This is a Query object, to get the results execute or iterate it.',
    'sql' => 'SELECT Models.receiver AS `Models__receiver`, Models.status AS `Models__status` Models.modified AS `Models__modified` FROM models Models WHERE (Models.receiver = :c0 AND Models.status in (:c1,:c2)) ORDER BY Models.modified DESC LIMIT 1', // extra columns have been removed for simplicity
    'params' => [
            ':c0' => [
                    'value' => (int) X,
                    'type' => 'integer',
                    'placeholder' => 'c0'
            ],  
            ':c1' => [
                    'value' => (int) 40, 
                    'type' => 'integer',
                    'placeholder' => 'c1'
            ],  
            ':c2' => [
                    'value' => (int) 50, 
                    'type' => 'integer',
                    'placeholder' => 'c2'
            ]   
    ],
    // .... more details
}

Now, I rerun the same query with limitation on modified column, by uncommenting this line:

'Printouts.modified >' => 'NOW() - INTERVAL 30 DAY'

This time I receive an empty result set. The $result variable is set to null in the following line:

$result = $this->Models->find('all', $options)->first();

The raw query output still remains the same on MySQL command line prompt:

SELECT
    receiver,
    status,
    modified
FROM
    printouts
WHERE
    receiver = (int) X AND
    status IN (40, 50) AND
    MODIFIED > NOW() - INTERVAL 30 DAY
ORDER BY
    modified DESC LIMIT 1;
+----------+--------+---------------------+
| receiver | status | modified            |
+----------+--------+---------------------+
|  (int) X |     50 | 2016-11-21 05:33:02 |
+----------+--------+---------------------+
1 row in set (0.01 sec)

On debugging the same query object this time, I receive following SQL:

object(Cake\ORM\Query) {

    '(help)' => 'This is a Query object, to get the results execute or iterate it.',
    'sql' => 'SELECT Models.receiver AS `Models__receiver`, Models.status AS `Models__status`, Models.modified AS `Models__modified` FROM models Models WHERE (Models.receiver = :c0 AND Models.status in (:c1,:c2) AND Models.modified > :c3) ORDER BY Models.modified DESC LIMIT 1',
    'params' => [
            ':c0' => [
                    'value' => (int) X,
                    'type' => 'integer',
                    'placeholder' => 'c0'
            ],
            ':c1' => [
                    'value' => (int) 40,
                    'type' => 'integer',
                    'placeholder' => 'c1'
            ],
            ':c2' => [
                    'value' => (int) 50,
                    'type' => 'integer',
                    'placeholder' => 'c2'
            ],
            ':c3' => [
                    'value' => 'NOW() - INTERVAL 30 DAY',
                    'type' => 'datetime',
                    'placeholder' => 'c3'
            ]
    ],
}

Assume value of X to be integer and same at all times. Model is a placeholder name.

How do I identify the gap in the two query executions and remove the difference in order achieve my initial objective of limiting rows by datetime stored in column modified.

Solution

By using 'Printouts.modified >' => 'NOW() - INTERVAL 30 DAY', you are telling it to look for columns where the modified date is greater than the literal string 'NOW() - INTERVAL 30 DAY'. Try 'Printouts.modified > NOW() - INTERVAL 30 DAY' instead.

Answered By - Greg Schmidt