Sunday, 11 August 2013

CakePHP SQL Operation using 'OR'

CakePHP SQL Operation using 'OR'

I am using CakePHP find to retrieve the data. I need to use OR between
conditions so i used following query as stated in CakePHP documentation.
This works perfectly other times but the problem arises when the field
name is same in OR array, Group.arrival_date in this case.
$this->Group->find('all',
array('conditions' => array(
'OR' => array(
'Group.arrival_date' => 'DATE_ADD(CURDATE(),
INTERVAL 30 DAY)',
'Group.arrival_date' => 'DATE_ADD(CURDATE(),
INTERVAL 7 DAY)'
)
)
));
The SQL it generate is
SELECT `Group`.`id`, `Group`.`rr_no`, `Group`.`package_id`,
`Group`.`user_id`,
`Group`.`group_name`, `Group`.`pax`, `Group`.`agent_id`, `Group`.`staff_id`,
`Group`.`arrival_date`, `Group`.`departure_date`, `Group`.`status`,
`Group`.`slug`,
`Group`.`book_flight`, `Group`.`allocated_tents`, `Group`.`alert`,
`Group`.`alert_seen`
FROM `groups` AS `Group` WHERE `Group`.`arrival_date` =
'DATE_ADD(CURDATE(), INTERVAL 7 DAY)'
It takes the second condition only. For different field name like:
$this->Group->find('all',
array('conditions' => array(
'OR' => array(
'Group.slug' => 'slug1',
'Group.group_name' => 'group1'
)
)
));
The generated SQL is
SELECT `Group`.`id`, `Group`.`rr_no`, `Group`.`package_id`,
`Group`.`user_id`,
`Group`.`group_name`, `Group`.`pax`, `Group`.`agent_id`, `Group`.`staff_id`,
`Group`.`arrival_date`, `Group`.`departure_date`, `Group`.`status`,
`Group`.`slug`,
`Group`.`book_flight`, `Group`.`allocated_tents`, `Group`.`alert`,
`Group`.`alert_seen`
FROM `groups` AS `Group` WHERE ((`Group`.`slug` = 'slug1') OR
(`Group`.`group_name` = 'group1'))
Which is as expected. What am i missing in the first find i used? How can
i get the above query work? Any help would be greatly appreciated. I am
using CakePHP 2.0.

No comments:

Post a Comment