duandu1966
2013-08-12 05:40 阅读 78
已采纳

使用'OR'的CakePHP SQL操作

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    doupin8555 doupin8555 2013-08-12 05:47

    Try:

    $this->Group->find('all', 
                   array('conditions' => array(
                           'OR' => array( 
                              array('Group.arrival_date' => 'DATE_ADD(CURDATE(), INTERVAL 30 DAY)'),
                              array('Group.arrival_date' => 'DATE_ADD(CURDATE(), INTERVAL 7 DAY)')
                           )
                       )
                    ));
    

    Or you could use IN:

    $this->Group->find('all', 
                     array('conditions' => array(
                         'Group.arrival_date' => array('DATE_ADD(CURDATE(), INTERVAL 30 DAY)', 'DATE_ADD(CURDATE(), INTERVAL 7 DAY)')
                      )
                  );
    
    点赞 评论 复制链接分享
  • dtf0925 dtf0925 2013-08-12 06:22

    For same field, simply put all values you need for OR in an array.

    $this->Group->find('all', 
                   array('conditions' => array(
    
                              'Group.arrival_date' => 
              array('DATE_ADD(CURDATE(), INTERVAL 30 DAY)', 'DATE_ADD(CURDATE(), INTERVAL 7 DAY)' )
                       )
                    ));
    
    点赞 评论 复制链接分享

相关推荐