zen of coding

Example of many nested conditions in CakePHP's find()

Just a quick example on how you can use deep, complex find conditions with OR, AND and NOT arrays in one shot…

We need to get a list of all companies, where:
Company.name is either ‘Future Holdings’ OR ‘Steel Mega Works’ AND we need to ensure that Company.status is either ‘active’ OR NOT ‘inactive’ OR ‘suspended’…

Here’s how you can accomplish this in cake:

$conditions = array(

   'OR' => array(
      array('Company.name' => 'Future Holdings'),
      array('Company.name' => 'Steel Mega Works')
   ),

   'AND' => array(
      array(

         'OR'=>array(
            array('Company.status' => 'active'),

            'NOT'=>array(
               array('Company.status'=> array('inactive', 'suspended'))
            )
         )
     )
   )
);

Which produces the following SQL:

[sourcecode language=”sql”]
SELECT `Company`.`id`, `Company`.`name`, `Company`.`description`, `Company`.`location`, `Company`.`created`, `Company`.`status`, `Company`.`size`

FROM
`companies` AS `Company`
WHERE
((`Company`.`name` = ‘Future Holdings’)
OR
(`Company`.`name` = ‘Steel Mega Works’))
AND
((`Company`.`status` = ‘active’)
OR (NOT (`Company`.`status` IN (‘inactive’, ‘suspended’))))
[/cc]

  • very cool, thanks for the tip!

  • teknoid

    @Kien Pham

    You’re welcome

  • many many thanks for that, I’m just implementing an advanced search module (deadline: yesterday) and you saved my life with this tip :)

  • teknoid

    @yaqoob

    Ah, just in time ;)

  • Stefanski

    company.Name => Array(“Abc”, “123”) works as well & easier for or.

  • teknoid

    @Stefanski

    Thanks, I do use that syntax in the NOT key. And you’re right it’s much easier to use IN() for this case, but for demonstration purposes I wanted to show a more descriptive method.

  • very useful information i was looking for something that can educaste me how to use OR conditions in find method. But my problem is more serious i need to use OR conditions with loops for example
    company.id => 1, company.id => 2 …..so on with many values whose count are not fixed. how to i loop through them
    ——————-

  • @online library management

    Do you mean ‘Company.id’ => array(1,2,3,4)?

  • Frederick D.

    Would you help me to please debug my complex find that is nested deeply? What I want to accomplish is this:

    SELECT * FROM repairorders WHERE (lof ‘ ‘ AND ((lof_act_60 IS NULL and lof_sch_60 < = CURDATE()) or (lof_act_75 IS NULL and lof_sch_75 <= CURDATE()) or (lof_act_90 IS NULL AND lof_sch_90 array('Repairorder.id DESC'), 'recursive' => -1, // Only Repairorder model
    ‘conditions’ => array(
    array(‘Repairorder.lof ‘ => ” “),
    ‘AND’ => array(
    array(
    ‘OR’ => array(
    array(
    ‘AND’ => array(
    array(‘Repairorder.lof_act_60’ => null),
    array(‘Repairorder.lof_sch_60 date(‘Y-m-d’))
    ),
    ‘AND’ => array(
    array(‘Repairorder.lof_act_75’ => null),
    array(‘Repairorder.lof_sch_75 date(‘Y-m-d’))
    ),
    ‘AND’ => array(
    array(‘Repairorder.lof_act_90’ => null),
    array(‘Repairorder.lof_sch_90 date(‘Y-m-d’))
    )
    )
    )
    )
    )
    )
    );

    With debug set to ‘2’ I can see this is not working. All I get for the WHERE clause is this:

    WHERE `Repairorder`.`lof` ‘ ‘ AND ((`Repairorder`.`lof_act_90` IS NULL) AND (`Repairorder`.`lof_sch_90` <= '2010-01-27')) ORDER BY `Repairorder`.`id` DESC I'm not getting my sets of 'OR' conditions within the first 'AND'. What am I doing wrong? Thank you in advance!

  • Frederick D.

    That didn’t come out quite right… sorry…

    The goal is this:

    SELECT * FROM repairorders WHERE (lof ‘ ‘ AND ((lof_act_60 IS NULL and lof_sch_60 < = CURDATE()) or (lof_act_75 IS NULL and lof_sch_75 <= CURDATE()) or (lof_act_90 IS NULL AND lof_sch_90 array('Repairorder.id DESC'), 'recursive' => -1, // Only Repairorder model
    ‘conditions’ => array(
    array(‘Repairorder.lof ‘ => ” “),
    ‘AND’ => array(
    array(
    ‘OR’ => array(
    array(
    ‘AND’ => array(
    array(‘Repairorder.lof_act_60’ => null),
    array(‘Repairorder.lof_sch_60 date(‘Y-m-d’))
    ),
    ‘AND’ => array(
    array(‘Repairorder.lof_act_75’ => null),
    array(‘Repairorder.lof_sch_75 date(‘Y-m-d’))
    ),
    ‘AND’ => array(
    array(‘Repairorder.lof_act_90’ => null),
    array(‘Repairorder.lof_sch_90 date(‘Y-m-d’))
    )
    )
    )
    )
    )
    )
    );

    The results from debug ‘2’ are this:

    WHERE `Repairorder`.`lof` ‘ ‘ AND ((`Repairorder`.`lof_act_90` IS NULL) AND (`Repairorder`.`lof_sch_90` <= '2010-01-27')) ORDER BY `Repairorder`.`id` DESC How do I fix my complex find? Thank you for your help!

  • Thank you, I was looking for a way to combine AND OR, and this works perfectly!

  • @Atea

    Glad it helped.

  • Hello @teknoid.. i have some problems about my query in cakephp… my field form like dis
    #view search ctp
    create(‘Pruf’,array(‘action’=>’search’));
    echo $form->input(‘query’,array(‘type’=>’text’));
    $tableSelection = array( ‘teksQuran’=>’Teks Quran’,’maklumatPembekal’=>’Maklumat Pembekal / Pengedar / Penerbit’, ‘noRujukanKDN’=>’No. Rujukan KDN’, ‘tarikhPengesahan’=>’Tarikh Pengesahan Pruf Akhir Teks Quran’);
    echo $form->select(‘tableSelection’,$tableSelection);
    echo $form->end(‘Cari’);
    ?>

    #controller
    data))
    {
    $query = $this->data[‘Pruf’][‘query’];
    $tableSelection = $this->data[‘Pruf’][‘tableSelection’];

    $conditions = $this->postConditions($this->data,array(
    ‘Pruf.teksQuran’ => ‘LIKE’,’Pruf.maklumatPembekal’ =>’LIKE’, ‘Pruf.tarikhPengesahan”LIKE’,’Pruf.noRujukanKDN’=>’LIKE’),’OR’, false);
    $result = $this->Post->find(‘all’,array(‘conditions’ => $conditions));
    $this->set(‘result’,$result);
    }
    }
    ?>

    so i dont know how to generate da query…to my result..Tq

  • Mate, thanks very much for this.

%d bloggers like this: