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]

%d bloggers like this: