Conditions in CakePHP RC6

Published on and tagged with cakephp  programming

Update (2008-09-17): Adapted code for CakePHP 1.2 RC2.

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->find('all', array('conditions' => array('User.firstname' => 'Daniel')));
$this->User->find('all', array('conditions' => array('User.firstname LIKE' => '%an%'));
$this->User->find('all', array('conditions' => array('User.age >' => 18));

When using several conditions in the array, they are combined with an “AND”.

[removed old stuff which no longer works]

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->find('all' => array('conditions' => array("User.firstname = 'Daniel' OR User.firstname = 'Hugo'")));

18 comments baked

  • Nate

    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'”)));

  • Sam D

    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

  • cakebaker

    @Nate: Thanks for the further explanations.

    @Sam D: Nice to hear you like it. Thanks :)

  • Diego

    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’”)));

  • cakebaker

    @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

  • Sumon

    How would you put a condition for distinct?

    $this->Model->find(‘list’, ‘distinct’)

  • cakebaker

    @Sumon: I think this is not possible with this method, but I could be wrong. You may want to ask in the Google Group.

  • olda

    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?

  • cakebaker

    @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!

  • olda

    oh je, but i mean somthing more like specify it in model class in $conditions property

    array ("or" =>
        array
        (
            "A" => "B"
            "B" => "C"
        )
    )

    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?

  • cakebaker

    @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!

  • Ellen

    I can’t do it like your example, but I can it when I change somethings:

    $this->set(“country”, $this->Country->find(“all”, array(‘conditions’=>array(‘Country.nom_country LIKE’=>’%South%’))));

    Thank you so much!!!

  • cakebaker

    @Ellen: Yes, the syntax was changed recently so I modified the article accordingly. Thanks for the hint!

  • Sarah

    I know this is old but it’s the best Google could find me.

    Basically I want my new model to be a “view” of an existing model.

    I therefore to add to the join conditions so I have created a hasmany condition in the parent model

    'Completeorder' => array(
        'className'     => 'Completeorder',
        'conditions'    => "Completeorder.status in ('Pending Pickup','Complete')",
        'order'         => 'Completeorder.pickupdate DESC'
    )

    However when I view the query it’s put the conditions in the where statement. This means that my left join isn’t returning null for those parents with no complete orders – its omitting them altogether.

    I’m happy to have a custom query but I’d also like to use Cake where possible. Anyway around this?

  • cakebaker

    @Sarah: Thanks for your comment.

    Right now I don’t see a way around this, though I have to admit I don’t understand your approach completely. Having a relationship YourModel hasMany CompleteOrder seems a bit odd to me ;-)

  • Sarah

    Thanks for the reply. It was part of an attempt to build queries based on pseudo “views” rather than having custom sql. On its own it might seem odd but when you put the building blocks together and are looking for available resources (those with complete/finished orders) it did make sense.

    :)

  • fairuz

    i have some problems about my query in controller… in search.ctp file i am generate my code like this

    //search.ctp
    $selectSearchFilter = array('a'=>'column1','b'=>'column b', 'c'=>'column c ' );
    $form->select('searchFilter',$selectFitlerSearch);
    $form->input('keyword',array('type'=>'text'));
    $form->end('Search');
    
    //in tempModels_controllers
    
    function search()
    {
      if(!empty($this->data()))
         {
            $keyword = $this->data['Pruf']['keyword'];
            $selectFilter = $this->data['Pruf']['searchFitler'];
    
            // i dont know how to generate filter SQL... :-(
           // select * from table where $searchFilter Like %$keyword%
    
    
            $result = $this->find('all',$conditions);
          }

    Questions : How to generate cakephp sql like about statements??

  • cakebaker

    @fairuz: Something like:

    $conditions = array('YourModel.' . $searchFilter . ' LIKE' => '%' . $keyword . '%');
    

    should do the trick (make sure $searchFilter is an allowed value before using it).

    Hope this helps!

Bake a comment




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

© daniel hofstetter. Licensed under a Creative Commons License