CODEWITHSUNDEEP
mysqlcakephp
Amit Kumar Sharma
Amit Kumar Sharma

Reputation: 262

CakePHP Condition compare database field with mysql format

While writing a code in CakePHP faced a problem.

I wanted to delete all rows whose expire time is less than 1 hour from now(). I don't want to use PHP function like date() etc as my PHP server timezone and MYsql Server timezone are different. Moreover my session token expire after 30 second so I can not bear time difference error if both server are not properly synchronize with realtime.

I wrote down this code 

$this->deleteAll(array('SESSION_TRANSFER.EXPIRE_TIME <' =>
DboSource::expression('CURRENT_TIMESTAMP - INTERVAL 1 MINUTE')));

My expectation is 

SELECT `SESSION_TRANSFER`.`SESS_TRANS_ID`
FROM `DISE`.`T_SESSION_TRANSFER` AS `SESSION_TRANSFER`
WHERE `SESSION_TRANSFER`.`EXPIRE_TIME` <
CURRENT_TIMESTAMP - INTERVAL 1
MINUTE
LIMIT 0 , 30

But it is generating error

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 
'SESSION_TRANSFER.EXPIRE_TIME <' in 'where clause'

SQL Query: SELECT `SESSION_TRANSFER`.`SESS_TRANS_ID` 
FROM `DISE`.`T_SESSION_TRANSFER` AS `SESSION_TRANSFER` 
WHERE `SESSION_TRANSFER.EXPIRE_TIME <` = CURRENT_TIMESTAMP - INTERVAL 1 hour

I also tried this

$this->deleteAll(array('SESSION_TRANSFER.EXPIRE_TIME <'
=> 'CURRENT_TIMESTAMP - INTERVAL 1 MINUTE '));

Query was not proper as expected

SELECT `SESSION_TRANSFER`.`SESS_TRANS_ID` 
FROM `DISE`.`T_SESSION_TRANSFER` AS `SESSION_TRANSFER` 
WHERE `SESSION_TRANSFER`.`EXPIRE_TIME` < 'CURRENT_TIMESTAMP - INTERVAL 1 MINUTE '

Any suggestion? I do not want to use PHP date function calculation

Upvotes: 1

Views: 355

Answers (1)

AD7six
AD7six

Reputation: 66347

Put your whole sql expression in the expression object:

$this->deleteAll(
    DboSource::expression('SESSION_TRANSFER.EXPIRE_TIME < CURRENT_TIMESTAMP - INTERVAL 1 MINUTE')
);

Be aware that doing this, there will be no table/fieldname quoting.

Note also that if you do not specify $cascade as false, Cake will perform a select and delete each row one at a time. It is probably more appropriate to issue a single query:

$this->deleteAll(
    DboSource::expression('SESSION_TRANSFER.EXPIRE_TIME < CURRENT_TIMESTAMP - INTERVAL 1 MINUTE'),
    false // <-
);

Or since that's a query with no variables you may as well just use query:

$this->query(
    'DELETE FROM T_SESSION_TRANSFER WHERE T_SESSION_TRANSFER.EXPIRE_TIME < CURRENT_TIMESTAMP - INTERVAL 1 MINUTE'
);

Upvotes: 1

Related Questions