CodeIgniter：查询分组和查找特定数据

Question

首先，我是 CodeIgniter 的新手，并且仍在学习中。我在 React Native 上创建了一个应用程序，它选择日期和时间来创建约会。我使用 CodeIgniter 作为我的后端。约会有date、start time和end time。创建预约的条件是：如果已创建约会，例如：date = 15-01-2020、start time = 7:00pm、end time = 8:00pm。然后用户不能创建另一个约会，例如：date = 15-01-2020, start time = 7:00pm, end time = 8:00pm（类似的日期和时间从晚上 7 点到 8 点已经预订）date = 15-01-2020, start time = 7:20pm, end time = 7:40pm（在已预约的预约内重叠）date = 15-01-2020, start time = 7:45pm, end time = 8:45pm(开始时间与已预约的时间重叠)date = 15-01-2020, start time = 6:45pm, end time = 7:45pm(结束时间与已预约的时间重叠)date = 15-01-2020, start time = 6:00pm, end time = 9:00pm（与已预约的约会重叠）此外，用户不应该能够创建与时间的约会，例如：start time = 8:00pm，end time = 7:00pm（其中start time > end time）根据上述条件，我只能达到条件 no.1。但是，我遇到了另一个问题，如果预订了start time = 7:00pmto end time = 8:00pm，我无法从start time = 8:00pmto创建end time = 9:00pm。下面提供的代码片段： 这是我（在朋友的帮助下）在后端 MODEL 上创建的函数：public function check_datetime_range($where)    {        $this->db->where('freelancer_id', $where['freelancer_id']);        $this->db->where("DATE_FORMAT(app_date_start, '%Y-%m-%d') = ", date('Y-m-d', strtotime($where['app_date_start'])));        $this->db->group_start();            $this->db->group_start();                $this->db->where('app_date_start >=', $where['app_date_start']);                $this->db->where('app_date_end <=', $where['app_date_end']);            $this->db->group_end();            $this->db->or_group_start();                $this->db->where('app_date_start >=', date('Y-m-d H:i:s', strtotime($where['app_date_start'])-3600));                $this->db->group_start();                    $this->db->where('app_date_end <=', $where['app_date_start']);                    $this->db->or_where('app_date_end <=', $where['app_date_end']);                $this->db->group_end();            $this->db->group_end();        $this->db->group_end();        return $this->db->get($this->_table_name)->num_rows();    }任何帮助将不胜感激，因为目前这些都有点压倒性。

Answer 1

解决了：

public function check_datetime_range($where)

{

    $this->db->where('freelancer_id', $where['freelancer_id']);

    $this->db->where("DATE_FORMAT(app_date_start, '%Y-%m-%d') = ", date('Y-m-d', strtotime($where['app_date_start'])));

    $this->db->group_start();

    //  Example: 

    //  start time = 7:00pm, end time = 8:00pm, date = 15-01-2020 (Existing Appointment)

    //1. Prevents overlapping time with an existing appointment

    //  start time = 7:00pm, end time = 8:00pm, date = 15-01-2020

    //  OR

    //  start time = 6:00pm, end time = 9:00pm, date = 15-01-2020

        $this->db->group_start();

            $this->db->where('app_date_start >=', $where['app_date_start']);

            $this->db->where('app_date_end <=', $where['app_date_end']);

        $this->db->group_end();

    //2. Prevents overlapping time from within an existing appointment

    //  start time = 7:15pm, end time = 7:45pm, date = 15-01-2020

        $this->db->or_group_start();

            $this->db->where('app_date_start <=', $where['app_date_start']);

            $this->db->where('app_date_end >=', $where['app_date_end']);

        $this->db->group_end();

    //3. Prevents overlapping end time inside an existing appointment

    //  start time = 6:45pm, end time = 7:45pm, date = 15-01-2020

        $this->db->or_group_start();

            $this->db->where('app_date_start >', $where['app_date_start']);

            $this->db->where('app_date_start <', $where['app_date_end']);

            $this->db->where('app_date_end >', $where['app_date_end']);

        $this->db->group_end();

    //4. Prevents overlapping start time inside an existing appointment

    //  start time = 7:45pm, end time = 8:45pm, date = 15-01-2020

        $this->db->or_group_start();

            $this->db->where('app_date_start <', $where['app_date_start']);

            $this->db->where('app_date_start <', $where['app_date_end']);

            $this->db->where('app_date_end >', $where['app_date_start']);

            $this->db->where('app_date_end <', $where['app_date_end']);

        $this->db->group_end();

    //5. Prevents start time > end time

    // start time = 7:00pm, end time = 6:00pm

        $this->db->or_group_start();

            $this->db->where('app_date_start >', $where['app_date_end']);

        $this->db->group_end();

    $this->db->group_end();

    return $this->db->get($this->_table_name)->num_rows();

}