Выберите из таблицы на основе условий из другой мета таблицы и пользовательского ввода

Question

Выберите из таблицы на основе условий из другой мета таблицы и пользовательского ввода

0

Мне нужно, чтобы получить доступные "автомобили" из таблицы 1 на основе существующих "резервирований", сохраненных в той же таблице, где "автомобиль" и "резервирование" имеют одну и ту же мета-таблицу, которая содержит дату начала и окончания резервирования в key => столбцы значений в мета-таблице, например:

carA имеет следующие оговорки:

4 июля - 8 июля
С 9 июля по 14 июля
18 июля-20 июля

Если пользователь запрашивает доступные автомобили с 15 июля по 17 июля, carA должен быть возвращен как доступный.
Если запросы доступны для автомобилей с 14 июля по 17 июля, carA недоступен.
Если запросы доступны с 16 июля по 18 июля, carA недоступен.
Если запросы доступны для автомобилей с 16 июля по 21 июля, carA недоступен.

Дальнейшая разработка. Вот упрощенная схема базы данных:

Таблица 1 - позиции
Таблица 2 - items-meta

Табличка 1 (позиции)

+------+-----------------+--------------+
| id   |   type          |     item     |
+------+-----------------+--------------+
|  1   |   car           |     carA     |
|  2   |   car           |     carB     |    
|  3   |   car           |     carC     |  
|  18  |   car           |     carD     |    
|  4   |   reservation   | reservationA |
|  5   |   reservation   | reservationB |
|  6   |   reservation   | reservationC |
|  7   |   reservation   | reservationD |
|  8   |   reservation   | reservationE |
|  9   |   reservation   | reservationF |
|  10  |   reservation   | reservationH |
|  11  |   reservation   | reservationI |
+------+-----------------+--------------+

Tabel 2 (items-meta)

+-----+---------+--------------+---------------------+
| id  |   rid   |    key       |        value        |
+-----+---------+--------------+---------------------+
|  1  |    4    |  start_time  | 2018-07-4 11:51:34  |
|  2  |    4    |  end_time    | 2018-07-8 11:51:34  |
|  3  |    4    |  car_id      | 1                   |
|  4  |    5    |  start_time  | 2018-07-9 11:51:34  |
|  5  |    5    |  end_time    | 2018-07-14 11:51:34 |
|  6  |    5    |  car_id      | 1                   |
|  7  |    6    |  start_time  | 2018-07-18 11:51:3  |
|  8  |    6    |  end_time    | 2018-07-20 11:51:34 |
|  9  |    6    |  car_id      | 1                   |
|  10 |    7    |  start_time  | 2018-07-18 11:51:3  |
|  11 |    7    |  end_time    | 2018-07-20 11:51:34 |
|  12 |    7    |  car_id      | 18                  |
+-----+---------+--------------+---------------------+

Я думаю, что это интересная задача, любая идея, как должен быть структурирован запрос на сервер, возможно ли это? оцените вашу помощь, спасибо.

Motaz M. El Shazly 31 июль 2018, в 21:16

Источник

0

Есть ли у вас контроль над структурой таблиц? Это можно было бы решить проще, если бы данные были структурированы в более нормализованном формате. Если у вас есть контроль над структурой данных, я могу дать некоторые рекомендации.
Chanicho 31 июль 2018, в 19:19
0

к сожалению, нет @Canicho, если мы сможем реструктурировать схему, это не будет проблемой .. спасибо.
Motaz M. El Shazly 31 июль 2018, в 19:22

Теги:

mysql

sql

schema

mariadb

relational-database

1 ответ

Ещё вопросы

Есть ли у вас контроль над структурой таблиц? Это можно было бы решить проще, если бы данные были структурированы в более нормализованном формате. Если у вас есть контроль над структурой данных, я могу дать некоторые рекомендации.
к сожалению, нет @Canicho, если мы сможем реструктурировать схему, это не будет проблемой .. спасибо.

Chanicho · Answer 1 · 2018-07-31T16-59-00.000Z

Я создал подзапрос, который преобразует элементы-метаданные в одну таблицу с идентификатором автомобиля, идентификатором резервирования, временем начала и временем окончания. Это представлено в запросе ниже как таблица R. Как только данные находятся в этом формате, вы можете запросить данные резервирования, чтобы увидеть, где есть какие-либо совпадения между вашими датами поиска и существующими оговорками.

Следующий запрос содержит список всех оговорок, которые противоречат данным датам поиска. Вы можете использовать этот список, чтобы узнать, какие автомобили следует исключить, поскольку они уже зарезервированы во время дат поиска.

set @SearchStartTime  = cast('2018-07-03 00:00:00' as datetime);
set @SearchEndTime  = cast('2018-07-05 00:00:00' as datetime);


select carID, rid, StartTime, EndTime from
(
          select Cars.'value' as carID, coalesce(Starts.rid, Ends.rid) as rid, StartTime, EndTime from
          (select id, rid, 'value'  from items_meta 
          where 'key'='car_id' ) Cars
          left outer join 
          (select id, rid, cast('value' as datetime) as StartTime from items_meta 
          where 'key'='start_time' ) Starts
          on Starts.rid = Cars.rid
          left outer join 
          (
          select id, rid, cast('value' as datetime)as EndTime from items_meta 
          where 'key'='end_time') Ends
          on Ends.rid = Cars.rid

 ) R
where 
(
@SearchStartTime between R.StartTime and R.EndTime
or
@SearchEndTime between R.StartTime and R.EndTime
or
R.StartTime between @SearchStartTime and @SearchEndTime
or
R.EndTime between @SearchStartTime and @SearchEndTime
)

Спасибо, но не ответ, который я искал, это должен быть запрос, чтобы обработать это в mysql.
Я обновил ответ так, чтобы он был написан для MySQL