How to use SQL functions in conditions, part II

Published on and tagged with cakephp  model  sql  tip

In the original post “How to use SQL functions in conditions” I showed you how to use SQL functions in conditions by means of the magic “-!” marker with the following example:

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

This snippet returns all user records modified at the current day. Now John-Henrique asked in a comment how to do the contrary, i.e. select those records not modified at the current day.

There exist at least two possible ways to accomplish this, the simplest one is to use “<>”:

$this->User->findAll(array('DATE(User.modified)' => '<> -!CURDATE()'));

It creates the following SQL code:

... WHERE DATE(`User`.`modified`) <> CURDATE()

Another approach is to use the “NOT” keyword of SQL:

$this->User->findAll(array('NOT' => array('DATE(User.modified)' => '-!CURDATE()')));

With this snippet we get the following SQL statement:

... WHERE NOT (DATE(`User`.`modified`) = CURDATE())

8 comments baked

  • Diego

    I got one question, ¿Is it possible to generate a query like this one with in model->save?
    UPDATE Model SET Model.field = Model.field + 1;

  • KireZ

    i tried your tricks but i couldnt make it work with something like this:

    $this->User->findAll(array(‘CONCAT(‘m-‘,User.ID)’ => ‘m-1532’));

    it loses the comma and i get mysql error

    SQl from the debug:


    WHERE CONCAT(‘m-‘ User.ID)=’m-1532’

    if you notice the comma after ‘m-‘ is missing

    i couldn find a workaround. Can you give me some tip or this is some kind of bug in cake methods?

    Thanks

  • cakebaker

    @Diego: Hm, I think that is not possible, at least I am not aware of it.

    @KireZ: It seems to be a bug in the version you use. I tried it with 1.2.0.5153 and it works fine.

  • Mike

    I was wondering if you could look at this behavior:
    http://bin.cakephp.org/view/770559837

    Even with adding “-!” before AES_ENCRYPT, it is still enclosing the query in quotes and not allowing the SQL function to execute. I can’t seem to figure this one out. Thanks for any help you can provide.

  • cakebaker

    @Mike: Hm, I think this is not supported by cake, at least I don’t know a way to do that. So I would use Model::query() for this purpose.

    But you may also ask in the IRC channel and/or in the group, maybe there someone has a better solution.

    Hope that helps!

  • Petr 'PePa' Pavel

    For those who missed the information, starting from CakePHP 1.2 it is no longer possible to use the magic tag -! in your condition to prevent your SQL function or keyword to be enclosed in quotes.

    The recommended way now is to place it into the key or not make the array associative at all. It is no longer possible to put it into the value part of the definition.

    Example here:
    http://blog.pepa.info/php-html-css/cakephp/sql-functions-or-keywords-in-cakephp-conditions/

  • cakebaker

    @Petr: Thanks for your comment! Yes, you are right, this behavior has been changed in the meantime.

  • bing

    If you are having issues with this, check out updating the dbo_mysql.php file in the core. – I modified function value(..) and updated the default parameter to check for AES_ before it returns the SQL. The reason it will break in Cake 1.3.x is because the core is using mysql_real_escape_string to prevent sql injection so … Modify at your own risk!!

Bake a comment




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

© daniel hofstetter. Licensed under a Creative Commons License