How to use SQL functions in conditions

Published on and tagged with cakephp  model  sql  tip

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.

8 comments baked

  • MP:Schorsch

    nice little syntax hint :-)

  • John-Henrique

    Ok, but how do negative condition?

    “WHERE DATE(`User`.`modified`) != CURDATE()”

    ????

    Sorry for small english, thaks!

  • cakebaker

    @John-Henrique: The following snippet should do the trick:

    $this->User->findAll(array('not' => array('DATE(User.modified)' => '-!CURDATE()')));
    
  • Gil Vidals

    The ‘-!’ did not work for me. I wonder what version of CakePHP is required for the ‘-!’ to work.

  • cakebaker

    @Gil: I don’t know when the ‘-!’ marker was introduced, but it should work with the current releases.

  • yunhaihuang

    how to return record from two join tables.

  • yunhaihuang

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

  • cakebaker

    @yunhaihuang: Your statement looks correct to me. You may also try the following statement:

    $this->Ad->findAllByCatId(3);
    

    Hope that helps!

Bake a comment




(for code please use <code>...</code> [no escaping necessary])

© daniel hofstetter. Licensed under a Creative Commons License