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

Question

首先，我是 CodeIgniter 的新手，并且仍在学习中。

我在 React Native 上创建了一个应用程序，它选择日期和时间来创建约会。我使用 CodeIgniter 作为我的后端。约会有date、start time和end time。

创建预约的条件是：

如果已创建约会，例如：date = 15-01-2020、start time = 7:00pm、end time = 8:00pm。

然后用户不能创建另一个约会，例如：

1. date = 15-01-2020, start time = 7:00pm, end time = 8:00pm（类似的日期和时间从晚上 7 点到 8 点已经预订）

2. date = 15-01-2020, start time = 7:20pm, end time = 7:40pm（在已预约的预约内重叠）

3. date = 15-01-2020, start time = 7:45pm, end time = 8:45pm(开始时间与已预约的时间重叠)

4. date = 15-01-2020, start time = 6:45pm, end time = 7:45pm(结束时间与已预约的时间重叠)

5. 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){&nbsp; &nbsp; $this->db->where('freelancer_id', $where['freelancer_id']);&nbsp; &nbsp; $this->db->where("DATE_FORMAT(app_date_start, '%Y-%m-%d') = ", date('Y-m-d', strtotime($where['app_date_start'])));&nbsp; &nbsp; $this->db->group_start();&nbsp; &nbsp; //&nbsp; Example:&nbsp;&nbsp; &nbsp; //&nbsp; start time = 7:00pm, end time = 8:00pm, date = 15-01-2020 (Existing Appointment)&nbsp; &nbsp; //1. Prevents overlapping time with an existing appointment&nbsp; &nbsp; //&nbsp; start time = 7:00pm, end time = 8:00pm, date = 15-01-2020&nbsp; &nbsp; //&nbsp; OR&nbsp; &nbsp; //&nbsp; start time = 6:00pm, end time = 9:00pm, date = 15-01-2020&nbsp; &nbsp; &nbsp; &nbsp; $this->db->group_start();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $this->db->where('app_date_start >=', $where['app_date_start']);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $this->db->where('app_date_end <=', $where['app_date_end']);&nbsp; &nbsp; &nbsp; &nbsp; $this->db->group_end();&nbsp; &nbsp; //2. Prevents overlapping time from within an existing appointment&nbsp; &nbsp; //&nbsp; start time = 7:15pm, end time = 7:45pm, date = 15-01-2020&nbsp; &nbsp; &nbsp; &nbsp; $this->db->or_group_start();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $this->db->where('app_date_start <=', $where['app_date_start']);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $this->db->where('app_date_end >=', $where['app_date_end']);&nbsp; &nbsp; &nbsp; &nbsp; $this->db->group_end();&nbsp; &nbsp; //3. Prevents overlapping end time inside an existing appointment&nbsp; &nbsp; //&nbsp; start time = 6:45pm, end time = 7:45pm, date = 15-01-2020&nbsp; &nbsp; &nbsp; &nbsp; $this->db->or_group_start();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $this->db->where('app_date_start >', $where['app_date_start']);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $this->db->where('app_date_start <', $where['app_date_end']);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $this->db->where('app_date_end >', $where['app_date_end']);&nbsp; &nbsp; &nbsp; &nbsp; $this->db->group_end();&nbsp; &nbsp; //4. Prevents overlapping start time inside an existing appointment&nbsp; &nbsp; //&nbsp; start time = 7:45pm, end time = 8:45pm, date = 15-01-2020&nbsp; &nbsp; &nbsp; &nbsp; $this->db->or_group_start();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $this->db->where('app_date_start <', $where['app_date_start']);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $this->db->where('app_date_start <', $where['app_date_end']);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $this->db->where('app_date_end >', $where['app_date_start']);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $this->db->where('app_date_end <', $where['app_date_end']);&nbsp; &nbsp; &nbsp; &nbsp; $this->db->group_end();&nbsp; &nbsp; //5. Prevents start time > end time&nbsp; &nbsp; // start time = 7:00pm, end time = 6:00pm&nbsp; &nbsp; &nbsp; &nbsp; $this->db->or_group_start();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $this->db->where('app_date_start >', $where['app_date_end']);&nbsp; &nbsp; &nbsp; &nbsp; $this->db->group_end();&nbsp; &nbsp; $this->db->group_end();&nbsp; &nbsp; return $this->db->get($this->_table_name)->num_rows();}