CODEWITHSUNDEEP
phpmysqlcakephp
trafalgar
trafalgar

Reputation: 736

How to do a timediff or datediff query in CakePHP

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));

Upvotes: 2

Views: 4092

Answers (1)

ndm
ndm

Reputation: 60493

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'].

Upvotes: 3

Related Questions