如何在CakePHP中进行timediff或datediff查询

Question

I want to run this query:

SELECT * FROM `foobar`
where
TIMESTAMPDIFF(MINUTE,request_time,NOW()) < 15

I have tried:

$this->find('first',
                array('conditions' => 
                   array('username' => $v['User']['username']),
                   'TIMESTAMPDIFF(MINUTE,request_time,NOW()) < ' => 15));

Answer 1

You have to place the SQL fragment inside of the conditions array, currently it's placed outside and thus it's being ignored.

$this->find('first', array
(
    'conditions' => array
    (
        'username' => $v['User']['username'],
        // this is where it needs to go
        'TIMESTAMPDIFF(MINUTE, request_time, NOW()) < ' => 15
    )
    // this is where it was before
));

That will result in a query similar to this:

SELECT
    `Model`.`username`, ...
FROM
    `db`.`model` AS `Model`
WHERE
    `username` = 'whatever'
    AND
    TIMESTAMPDIFF(MINUTE, request_time, NOW()) < 15
LIMIT 1

By default CakePHP will include all fields in the SELECT statement.

Obviously if you want the query to use only the TIMESTAMPDIFF condition as in your example, then you have to drop 'username' => $v['User']['username'].