How to use SQL functions in conditions
Sometimes it is handy to use a (database-specific) SQL function in your find/findAll statements, e.g. a function which returns the current date. But how can this be done?
The obvious approach doesn’t work:
$this->User->findAll(array('DATE(User.modified)' => 'CURDATE()'));
The function “CURDATE()” is escaped and treated as a string as you can see in the generated SQL statement:
... WHERE DATE(`User`.`modified`) = 'CURDATE()'
To avoid the escaping we have to add the magical “-!” marker in front of the function name:
$this->User->findAll(array('DATE(User.modified)' => '-!CURDATE()'));
This will generate the expected SQL:
... WHERE DATE(`User`.`modified`) = CURDATE()
Hooray!
Thanks to Kabturek who mentioned this little trick some time ago in his blog.
nice little syntax hint :-)
Ok, but how do negative condition?
“WHERE DATE(`User`.`modified`) != CURDATE()”
????
Sorry for small english, thaks!
@John-Henrique: The following snippet should do the trick:
$this->User->findAll(array('not' => array('DATE(User.modified)' => '-!CURDATE()')));The ‘-!’ did not work for me. I wonder what version of CakePHP is required for the ‘-!’ to work.
@Gil: I don’t know when the ‘-!’ marker was introduced, but it should work with the current releases.
how to return record from two join tables.
how to return record from two join tables.
example:
cats(id, name_de)
ads(id, title_de, cat_id)
i want to retrieve the data from two above table, how do i do it?
$temp=$this->Ad->Cat->execute(”SELECT title_de, name_de FROM ads, cats WHERE cats.id=ads.cat_id and cat_id=3″);
if i do this, i only return the value of “cat.name_de”, don’t return the “ad.title_de”
array ( [0] => Array ( [Cat] => Array ( [name_de] => 123456 ) ) [1] => Array ( [Cat] => Array ( [name_de] => 123456 ) ) )
@yunhaihuang: Your statement looks correct to me. You may also try the following statement:
Hope that helps!