php - 创建复杂的 SQL 语句 - CakePHP

Question

我正在尝试查找用户输入的日期是否与数据库中的任何日期重叠。

我已经得到了 SQL 语句(可能需要一些调整，将进行测试)，但我正在努力将其正确地输入到 cakePHP 中。

我有什么

$result = $this->find('all', array(
    'conditions' => array(
        'OR' => array(
            'AND' => array(
                array('BoardroomBooking.start_time >=' => $this->data['BoardroomBooking']['start_time']),
                array('BoardroomBooking.start_time <' => $this->data['BoardroomBooking']['start_time'])
            ),
            'AND' => array(
                array('BoardroomBooking.start_time <=' => $this->data['BoardroomBooking']['start_time']),
                array('BoardroomBooking.start_time >' => $this->data['BoardroomBooking']['end_time'])
            )
        )
    )
));

它创造了什么

SQL Query: SELECT `BoardroomBooking`.`id`, `BoardroomBooking`.`name`,
`BoardroomBooking`.`boardroom_id`, `BoardroomBooking`.`date`, 
`BoardroomBooking`.`start_time`, `BoardroomBooking`.`end_time`, 
`BoardroomBooking`.`notes`, `BoardroomBooking`.`recurrence_id`, 
`BoardroomBooking`.`created`, `BoardroomBooking`.`modified`, `Boardroom`.`id`, 
`Boardroom`.`name`, `Boardroom`.`created`, `Boardroom`.`modified` FROM 
`intraweb_db`.`boardroom_bookings` AS `BoardroomBooking` LEFT JOIN 
`intraweb_db`.`boardrooms` AS `Boardroom` ON (`BoardroomBooking`.`boardroom_id`
 = `Boardroom`.`id`) WHERE ((`BoardroomBooking`.`start_time` <= '12:55:00') AND 
(`BoardroomBooking`.`start_time` > '13:00:00'))

我想要什么

SQL Query: SELECT `BoardroomBooking`.`id`, `BoardroomBooking`.`name`,
`BoardroomBooking`.`boardroom_id`, `BoardroomBooking`.`date`, 
`BoardroomBooking`.`start_time`, `BoardroomBooking`.`end_time`, 
`BoardroomBooking`.`notes`, `BoardroomBooking`.`recurrence_id`, 
`BoardroomBooking`.`created`, `BoardroomBooking`.`modified`, `Boardroom`.`id`, 
`Boardroom`.`name`, `Boardroom`.`created`, `Boardroom`.`modified` FROM 
`intraweb_db`.`boardroom_bookings` AS `BoardroomBooking` LEFT JOIN 
`intraweb_db`.`boardrooms` AS `Boardroom` ON (`BoardroomBooking`.`boardroom_id`
 = `Boardroom`.`id`) WHERE (`BoardroomBooking`.`start_time` >= '12:55:00' AND 
`BoardroomBooking`.`start_time` < '12:55:00') OR (`BoardroomBooking`.`start_time`
 <= '12:55:00') AND (`BoardroomBooking`.`start_time` > '13:00:00'))

我已阅读有关如何 retrieve data 的文档，但我一生都无法让它按照我想要的方式工作。

Answer 1

现在，我可能是不正确的，但我相信你的问题出在你的数组声明中:

  array(
    'AND' => array(
      ...
    ),
    'AND' => array(
      ...
    )
  )

就 PHP 而言，您刚刚在数组中声明了一个“AND”键，然后重新分配了它。根据Cake Cookbook ，您需要在不同的数组中声明相同的字段:

'OR' => array(
  array('AND' => array (...)),
  array('AND' => array (...))
)

这应该可以防止查询将您的两个条件视为同一事物并覆盖条目。