Mi imbatto in questo tipo di esigenza tutto il tempo. Supponiamo che tu abbia un intervallo di date che vuoi controllare (cioè trovare le unità libere per) che è definito con una data di inizio e una data di fine. Nel database, al fine di chiarire di cosa stiamo parlando, diciamo che le prenotazioni esistenti hanno una prima e una data ultima (della prenotazione).
Quindi ci sono sei possibili casi per ogni prenotazione (| xxxx |) rispetto all'intervallo di date che stai controllando ([----]):
-
[--|xxxx|--]
Può essere interamente contenuto all'interno del campo di controllo. Ciò significa che l'intervallo di date non è disponibile per questa unità.
-
|xx[xx|----]
Può cavalcare l'inizio dell'intervallo di controllo. Di nuovo, questa unità non è disponibile.
-
[----|xx]xx|
Può percorrere la fine dell'intervallo di controllo. Non disponibile.
-
|xx[----]xx|
Può contenere completamente il campo di controllo. Ancora, non disponibile.
-
|xxxx|[----]
Può precedere il campo di controllo. Questa unità potrebbe essere disponibile!
-
[----]|xxxx|
Può seguire interamente il campo di controllo. Questa unità potrebbe essere disponibile!
Noterai che in tutti i casi in cui l'unità non è disponibile, la prima data è sempre prima * la data di fine, e l'ultima data è sempre dopo * la data di inizio. Questo non è vero per i due casi in cui gli intervalli non si sovrappongono. Così! Abbiamo una condizione di distinzione.
(* o uguale a)
In questa situazione, le unità che sono in uso avranno almeno una prenotazione esistente dove first < = 'end' AND last > = 'start'. MySQL ha un sacco di funzioni di raggruppamento e aggregazione davvero belle che possiamo usare qui per ottenere effettivamente MySQL per fare tutto il lavoro pesante per noi. Ad esempio, potremmo fare qualcosa di simile:
SELECT Units.*, SUM(IF(ReservationID IS NULL, 0, First <= 'end' AND Last >= 'start')) AS ConflictingReservations
FROM Units LEFT JOIN Reservations USING (UnitID)
GROUP BY UnitID
HAVING ConflictingReservations = 0
Nella query precedente (che ammetto di non aver provato cough , ma l'idea è valida, onesta!) abbiamo lasciato unire la tabella Units alla tabella Reservations in modo che se un'unità non è mai stato prenotato, verrà comunque mostrato nei risultati. Da lì, raggruppiamo le righe per UnitID e creiamo una colonna derivata denominata "ConflictingReservations" che contiene la somma di un confronto logico. Come accennato in precedenza, se il confronto logico è VERO (che in MySQL ha il valore '1') significa che la specifica prenotazione che stiamo verificando si sovrappone all'intervallo di controllo. Sommando tutti gli "hit" ci forniremo il numero di prenotazioni per una particolare unità che si sovrappongono all'intervallo di controllo.
Nota: proteggiamo il confronto logico con il caso in cui un'unità non è mai stata prenotata prima e quindi il join sinistro restituisce una riga con valori NULL nella prima e nell'ultima colonna. Se ReservationID è NULL (e quindi la prima e l'ultima data saranno nulle, forzando l'intera colonna derivata da ConflictingReservations a NULL anziché a 0), la contiamo come non-match / false, ovvero: 0.
Il passo finale dice "butta via tutte le righe (cioè le unità) che hanno un numero diverso da zero di prenotazioni in conflitto". Questo dovrebbe lasciarti con un elenco di righe dalla tabella delle Unità che non hanno prenotazioni all'interno dell'intervallo di controllo (cioè, che sono disponibili per prenotare in quell'intervallo.)
Se sono riuscito a correggere i casi limite (come ho fatto con il caso zero prenotazioni) o la sintassi sbagliata, fammi sapere e modificherai la risposta!