Thursday, 5 December 2013

Cakephp multiple condition in find

Multiple condition in find CakePHP

Example 1: If you want this query in CakePHP

SELECT first_name, last_name,joining_date from employees
WHERE id = 047 AND ((joining_date = Last_day(joining_date)
        AND Month(joining_date) IN(3, 6, 9, 12))
        OR (joining_date = '2015-12-10'))
       
$this->Employee->find('all', 
  array('fields' => 
       array('first_name','last_name','joining_date'),
        'conditions' => 
      array('id' => 047,
    'OR' => array(
     array(
      'joining_date = LAST_DAY(joining_date)',
      'MONTH(joining_date)' => array(3,6,9,12)),
    'joining_date' => '2015-12-10'))
    ));

Example 2:
    
$conditions = array(
   'OR' => array(
      array('Employee.name' => 'Sudhir Pandey'),
      array('Employee.name' => 'Nitish Srivastava')
   ),
   'AND' => array(
      array(
        'OR'=>array(
            array('Employee.status' => 'active'),
            'NOT'=>array(
               array('Employee.status'=> array('inactive', 'suspended'))
            )
        )
     )
   )
);

$this->Employee->find('all',array('conditions'=>$conditions));

It will equivalent result to 

SELECT `Employee`.`id`, `Employee`.`name`, `Employee`.`department`,`Employee`.`created`, `Employee`.`status`
FROM `Employees` AS `Employee` WHERE 
((`Employee`.`name` = ‘Sudhir Pandey’) OR (`Employee`.`name` = ‘Nitish Srivastava’))
AND (`Employee`.`status` = ‘active’) OR (NOT (`Employee`.`status` IN (‘inactive’, ‘suspended’))))

Example 3: Only Create conditions

$conditions = array(
    'AND' => array(
        array(
            'OR' => array(
                array('Event.private' => null),
                array('Event.private' => 0)
            )
        ),
        array(
            'OR' => array(
                array('Gallery.private' => null),
                array('Gallery.private' => 0)
            )
        ),
        array(
            'OR' => array(
                array('User.deleted' => null),
                array('User.deleted' => 0)
            )
        )
    )
);

0 comments:

Post a Comment