Поиск доступности с MySQL (и PHP)?

У меня есть две таблицы MySQL (MyIsAm), которые представляют собой единицы размещения и заказы:

LettingUnits ( ID , Name и т. Д.)
LettingUnitBookings ( ID , F_LU_ID , Start , End )

Где F_LU_ID – это внешний ключ к устройству.

Каков наилучший способ поиска единиц, доступных в течение определенного периода времени? Поиск проходит через начало, конец и продолжительность.

Начало = Самое раннее начало бронирования
Конец = Последний конец бронирования
Продолжительность = Продолжительность бронирования

Мне было бы интересно узнать, возможно ли это сделать в MySQL, но если нет, то лучший способ сделать это в PHP.

пример

В ответ на ответы ниже я чувствую, что пример поможет объяснить проблему.

A LettingUnit:

(123, "Foo Cottage")

Некоторые LettingUnitBookings:

(400, 123, 01/01/09, 05/01/09) – бронирование на 5 дней
(401, 123, 10/01/09, 20/01/09) – 10-дневный заказ
(402, 123, 25/01/09, 30/01/09) – бронирование на 5 дней

Если мы ищем:

Начало = 01/01/09
End = 01/02/09
Продолжительность = 5 (дней)

Затем мы хотим, чтобы устройство отображалось. Потому что есть доступность для 5-дневного бронирования в пределах диапазона поиска.

Если продолжительность составляет 10, то устройство не будет отображаться, поскольку в пределах диапазона поиска не будет 10 последовательных незарегистрированных дней.

Solutions Collecting From Web of "Поиск доступности с MySQL (и PHP)?"

Вот решение, которое, похоже, работает:

 SELECT t.*, DATEDIFF(t.LatestAvailable, t.EarliestAvailable) AS LengthAvailable FROM (SELECT u.*, COALESCE(b1.End, @StartOfWindow) AS EarliestAvailable, COALESCE(b2.Start, @EndOfWindow) AS LatestAvailable FROM LettingUnits u LEFT OUTER JOIN LettingUnitBookings b1 ON (u.ID = b1.F_LU_ID AND b1.End BETWEEN @StartOfWindow AND @EndOfWindow) LEFT OUTER JOIN LettingUnitBookings b2 ON (u.ID = b2.F_LU_ID AND b2.Start BETWEEN @StartOfWindow AND @EndOfWindow AND b2.Start >= b1.End) -- edit: new term ) AS t LEFT OUTER JOIN LettingUnitBookings x ON (t.ID = x.F_LU_ID AND x.Start < t.LatestAvailable AND x.End > t.EarliestAvailable) WHERE x.ID IS NULL AND DATEDIFF(t.LatestAvailable, t.EarliestAvailable) >= @WindowSize;

Выход:

 +-----+-------------+-------------------+-----------------+-----------------+ | ID | Name | EarliestAvailable | LatestAvailable | LengthAvailable | +-----+-------------+-------------------+-----------------+-----------------+ | 123 | Foo Cottage | 2009-01-05 | 2009-01-10 | 5 | | 123 | Foo Cottage | 2009-01-20 | 2009-01-25 | 5 | | 456 | Bar Cottage | 2009-01-20 | 2009-01-31 | 11 | +-----+-------------+-------------------+-----------------+-----------------+

Анализ этого с помощью EXPLAIN показывает, что он довольно хорошо использует индексы:

 +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | Using where | | 1 | PRIMARY | x | ref | F_LU_ID | F_LU_ID | 8 | t.ID | 2 | Using where; Not exists | | 2 | DERIVED | u | system | NULL | NULL | NULL | NULL | 1 | | | 2 | DERIVED | b1 | ref | F_LU_ID | F_LU_ID | 8 | const | 0 | | | 2 | DERIVED | b2 | ref | F_LU_ID | F_LU_ID | 8 | const | 0 | | +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------------------+

Сравните с сообщением EXPLAIN для решения, данного @martin clayton:

 +----+--------------+---------------------+--------+---------------+---------+---------+------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+---------------------+--------+---------------+---------+---------+------+------+---------------------------------+ | 1 | PRIMARY | lu | system | PRIMARY,ID | NULL | NULL | NULL | 1 | | | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | Using where | | 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 4 | Using temporary; Using filesort | | 2 | DERIVED | <derived5> | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using join buffer | | 5 | DERIVED | LettingUnitBookings | ALL | NULL | NULL | NULL | NULL | 3 | | | 6 | UNION | LettingUnitBookings | index | NULL | F_LU_ID | 8 | NULL | 3 | Using index | | NULL | UNION RESULT | <union5,6> | ALL | NULL | NULL | NULL | NULL | NULL | | | 3 | DERIVED | LettingUnitBookings | ALL | NULL | NULL | NULL | NULL | 3 | | | 4 | UNION | LettingUnitBookings | index | NULL | F_LU_ID | 8 | NULL | 3 | Using index | | NULL | UNION RESULT | <union3,4> | ALL | NULL | NULL | NULL | NULL | NULL | | +----+--------------+---------------------+--------+---------------+---------+---------+------+------+---------------------------------+

В общем, вы хотите избежать планов оптимизации, которые вынуждают Using filesort или Using temporary поскольку это убийцы производительности. Запрос с использованием GROUP BY почти наверняка вызывает такую оптимизацию, по крайней мере, в MySQL.

Это некрасиво.

Присоединяйтесь к LettingUnitBookings себе
Найдите начало и конец пробелов между заказами для каждого F_LU_ID
Получите размер пробелов – доступные слоты,
Рассмотрите случай, когда нет существующих заказов, которые «скобки» подходят для слота, добавьте даты отправления для этого
Присоедините эту проекцию к таблице LettingUnits и примените критерии WHERE (начало, конец, продолжительность)

Я пренебрег включением BookingUnits, у которых нет заказов вообще.

В конце концов выглядит так:

 SELECT @StartOfWindow := '2009-01-01', @EndOfWindow := '2009-02-01', @WindowSize := 5 ; SELECT lu.Name, Slots.* FROM ( SELECT lub1.F_LU_ID, DATE_ADD( MAX( lub2.date_time ), INTERVAL 1 DAY ) AS StartOfSlot, DATE_SUB( lub1.date_time, INTERVAL 1 DAY ) AS EndOfSlot, DATEDIFF( lub1.date_time, MAX( lub2.date_time ) ) - 1 AS AvailableDays FROM ( SELECT F_LU_ID, Start AS date_time FROM LettingUnitBookings UNION SELECT F_LU_ID, CAST( '9999-12-31' AS DATE ) FROM LettingUnitBookings ) AS lub1, ( SELECT F_LU_ID, End AS date_time FROM LettingUnitBookings UNION SELECT F_LU_ID, CAST( '1000-01-01' AS DATE ) FROM LettingUnitBookings ) AS lub2 WHERE lub2.date_time <= lub1.date_time AND lub2.F_LU_ID = lub1.F_LU_ID GROUP BY lub1.F_LU_ID, lub1.date_time ) Slots JOIN LettingUnits lu ON lu.ID = Slots.F_LU_ID WHERE Slots.AvailableDays >= @WindowSize AND ( ( DATEDIFF( Slots.EndOfSlot, @EndOfWindow ) >= @WindowSize AND DATEDIFF( @StartOfWindow, Slots.StartOfSlot ) >= @WindowSize ) OR ( DATEDIFF( @EndOfWindow, Slots.StartOfSlot ) >= @WindowSize AND DATEDIFF( Slots.EndOfSlot, @StartOfWindow ) >= @WindowSize ) )

дает

 Name F_LU_ID StartOfSlot EndOfSlot AvailableDays Foo Cottage 123 2009-01-06 2009-01-09 5 Foo Cottage 123 2009-01-21 2009-01-24 5

Надеюсь, это можно адаптировать в соответствии с вашими потребностями.

Кроме того, если бронирование может начаться в тот же день, что и предыдущий заказ, вы можете немного приспособиться …

 SELECT lu.Name, Slots.* FROM ( SELECT lub1.F_LU_ID, MAX( lub2.date_time ) AS StartOfSlot, lub1.date_time AS EndOfSlot, DATEDIFF( lub1.date_time, MAX( lub2.date_time )) AS AvailableDays FROM ( SELECT F_LU_ID, Start AS date_time FROM LettingUnitBookings UNION SELECT F_LU_ID, CAST( '9999-12-31' AS DATE ) FROM LettingUnitBookings ) AS lub1, ( SELECT F_LU_ID, End AS date_time FROM LettingUnitBookings UNION SELECT F_LU_ID, CAST( '1000-01-01' AS DATE ) FROM LettingUnitBookings ) AS lub2 WHERE lub2.date_time <= lub1.date_time AND lub2.F_LU_ID = lub1.F_LU_ID GROUP BY lub1.F_LU_ID, lub1.date_time ) Slots JOIN LettingUnits lu ON lu.ID = Slots.F_LU_ID WHERE Slots.AvailableDays >= @WindowSize AND ( DATEDIFF( Slots.EndOfSlot, @EndOfWindow ) >= @WindowSize AND DATEDIFF( @StartOfWindow, Slots.StartOfSlot ) >= @WindowSize ) OR ( DATEDIFF( @EndOfWindow, Slots.StartOfSlot ) >= @WindowSize AND DATEDIFF( Slots.EndOfSlot, @StartOfWindow ) >= @WindowSize )