Блог

В моем проекте у меня есть следующие таблицы для хранения рабочего графика персонала и праздников.

Вот моя схема базы данных и некоторые данные, а также SQLFiddle того же:

 -- schema
CREATE TABLE schedules (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    day_of_week INT NOT NULL,
    start_date VARCHAR(25) NOT NULL,
    end_date VARCHAR(25),
    PRIMARY KEY(id)
);

-- data
INSERT INTO schedules
    (id, user_id, day_of_week,start_date,end_date)
VALUES
    (1, 2,1,"2021-05-03","2021-05-24"),
    (2, 2,1,"2021-05-31","2021-05-31"),
    (3, 2,1,"2021-06-07",null),
    (4, 2,2,"2021-05-04",null),
    (5, 2,3,"2021-05-05",null),
    (6, 2,4,"2021-05-06",null),
    (7, 2,5,"2021-05-07",null),
    (8, 2,6,"2021-05-08",null),
    (9, 3,1,"2021-04-12","2021-05-31"),
    (10, 3,1,"2021-06-07",null),
    (11, 3,2,"2021-05-11","2021-06-08"),
    (12, 3,2,"2021-06-15",null),
    (13, 3,3,"2021-05-05",null),
    (14, 3,4,"2021-05-06",null),
    (15, 3,5,"2021-05-07",null),
    (16, 3,6,"2021-05-08",null);
 

Я не мог понять, как получить данные о расписании персонала, если персонал работает в данный день или неделю.

Ожидаемый результат должен быть таким, как показано ниже, для разных недель

 2021-05-09 to 2021-05-21

 ---- --------- ------------- ------------ ------------ 
| id | user_id | day_of_week | start_date | end_date   |
 ---- --------- ------------- ------------ ------------ 
|  1 |       2 |           1 | 2021-05-03 | 2021-05-24 |
 ---- --------- ------------- ------------ ------------ 
|  9 |       3 |           1 | 2021-04-12 | 2021-05-31 |
 ---- --------- ------------- ------------ ------------ 

2021-05-30 to 2021-06-05

 ---- --------- ------------- ------------ ------------ 
| id | user_id | day_of_week | start_date | end_date   |
 ---- --------- ------------- ------------ ------------ 
|  2 |       2 |           1 | 2021-05-31 | 2021-05-31 |
 ---- --------- ------------- ------------ ------------ 
| 10 |       3 |           1 | 2021-06-07 | NULL       |
 ---- --------- ------------- ------------ ------------ 

2021-06-13 to 2021-06-19

 ---- --------- ------------- ------------ ------------ 
| id | user_id | day_of_week | start_date | end_date   |
 ---- --------- ------------- ------------ ------------ 
|  3 |       2 |           1 | 2021-06-07 | NULL       |
 ---- --------- ------------- ------------ ------------ 
| 10 |       3 |           1 | 2021-06-07 | NULL       |
 ---- --------- ------------- ------------ ------------ 
 

в настоящее время я выполняю следующий запрос в инкрементном цикле от 2021-06-13 до 2021-06-19 в Laravel

 select
  *
from
`schedules`
where
  and `start_date` <= '2021-05-13' // 2021-05-14,2021-05-15 etc.
  and `day_of_week` = 0 // 1,2,3 etc.
  and (
    `end_date` >= '2021-05-18'
    or `end_date` is null )
 

Ниже приведен мой код Laravel для того же

     $start_date = new Carbon($data['start_date']);
    $start_date->startOfWeek(Carbon::SUNDAY);

    $schedule = [];

    for ($i = 0; $i <= 6; $i  ) {

        $schedule[] = StaffSchedule::
            where('start_date', '<=', $start_date->format('Y-m-d'))
            ->where('day_of_week',$start_date->dayOfWeek)
            ->where(function ($query) use ($start_date) {
                $query->where('end_date','>=',$start_date->format('Y-m-d'));
                $query->orWhereNull('end_date');
            })->orderBy('user_id')->get();

        $start_date = $start_date->addDay();
    }
 

Приведенный выше код запускает 7 запросов, по 1 на каждый день недели.

Можно ли объединить и выполнить один запрос?

Спасибо

В качестве отправной точки рассмотрим следующее:

 SELECT * 
  FROM schedules 
 WHERE start_date <= '2021-05-15' 
   AND COALESCE(end_date,start_date) >= '2021-05-09';
 ---- --------- ------------- ------------ ------------ 
| id | user_id | day_of_week | start_date | end_date   |
 ---- --------- ------------- ------------ ------------ 
|  1 |       2 |           1 | 2021-05-03 | 2021-05-24 |
 ---- --------- ------------- ------------ ------------ 

SELECT * 
  FROM schedules 
 WHERE start_date <= '2021-06-05' 
   AND COALESCE(end_date,start_date) >= '2021-05-31';
 ---- --------- ------------- ------------ ------------ 
| id | user_id | day_of_week | start_date | end_date   |
 ---- --------- ------------- ------------ ------------ 
|  2 |       2 |           1 | 2021-05-31 | 2021-05-31 |
 ---- --------- ------------- ------------ ------------ 

SELECT * 
  FROM schedules 
 WHERE start_date <= '2021-06-06'   INTERVAL 1 WEEK 
   AND COALESCE(end_date,start_date) >= '2021-06-06';
 ---- --------- ------------- ------------ ---------- 
| id | user_id | day_of_week | start_date | end_date |
 ---- --------- ------------- ------------ ---------- 
|  3 |       2 |           1 | 2021-06-07 | NULL     |
 ---- --------- ------------- ------------ ----------