Conditions in CakePHP RC6
I have already written about defining conditions in RC6, but unfortunately, most of it is no longer valid ;-) So, in this post I will show the current way of defining conditions.
First some examples using the array syntax:
$this->User->findAll(array('User.firstname' => '= Daniel'));
$this->User->findAll(array('User.firstname' => 'LIKE %an%'));
$this->User->findAll(array('User.age' => '> 18'));
When using several conditions in the array, they are combined with an “AND”.
If you do not want for some reason that CakePHP does quote a value automatically, you can use “-!” as shown in the next example. I think you do not have to use “-!” very often, at least I do not see any use cases for it ;-)
$this->User->findAll(array('User.firstname' => "-! 'Daniel'"));
The most flexible way is to use a simple string with your condition(s). In this case you have to single quote the values, as shown in the example:
$this->User->findAll("User.firstname = 'Daniel' OR User.firstname = 'Hugo'");
A couple technical details:
When using ‘=’ as a comparison operator, you actually don’t need to include it, as that is the default, so:
$this->User->findAll(array(’User.firstname’ => ‘= Daniel’));
becomes
$this->User->findAll(array(’User.firstname’ => ‘Daniel’));
Also, you can now use nested arrays for complex conditions, so:
$this->User->findAll(”User.firstname = ‘Daniel’ OR User.firstname = ‘Hugo’”);
could be
$this->User->findAll(array(’or’ => array(”User.firstname” => ‘Daniel’), array(’User.firstname = ‘Hugo’”)));
Ok now that is more like it. I have been waiting for nesting! Hey Daniel, thanks for this site it is a great little house o’ cake!
Sam D
@Nate: Thanks for the further explanations.
@Sam D: Nice to hear you like it. Thanks :)
Great insight into the conditions array, i was toying arround with 1.2.0.6311 beta, and wondering if this still works.
$this->User->findAll(array(’or’ => array(”User.firstname” => ‘Daniel’), array(’User.firstname = ‘Hugo’”)));
@Diego: No, this no longer works. Now you can use:
$this->User->findAll(array('User.firstname' => array('Daniel', 'Hugo')));or if you want to use different column names you could use:
$this->User->findAll(array('or' => array('User.id' => '1', 'User.firstname' => 'Daniel')));HTH
How would you put a condition for distinct?
$this->Model->find(’list’, ‘distinct’)
@Sumon: I think this is not possible with this method, but I could be wrong. You may want to ask in the Google Group.
what about multiple ‘or’ key in array? for multiple ‘or’ in conditions, like : exp (A=B OR B=C) AND(A=D OR B=D)? how can i write this in cake find conditions?
@olda: You could write it in the following way:
$this->YourModel->find('all', array('conditions' => '(A=B OR B=C) AND (A=D OR B=D)'));But you have to be careful with this approach. It is very easy to introduce an SQL injection vulnerability because the values are not automatically escaped by cake!
Anyway, hope it helps!
oh je, but i mean somthing more like specify it in model class in $conditions property
and some far in code use find function with additional conditions.
btw, conditions that i use in call find function will join with conditions specify in model?
@olda: Hm, what do you mean with “$conditions property”? The model doesn’t contain such a property. Or do you mean the “conditions” option you can specify when defining an association? In that case you can specify the conditions as a string as shown in my previous comment.
Hope that helps!