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())
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;
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
@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.
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.
@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!
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/
@Petr: Thanks for your comment! Yes, you are right, this behavior has been changed in the meantime.