Hjelp til å utforme en booking/ledige enheter spørring

[Thullball]

Prøver å lage et reserveringssystem og i den anledning forsøker jeg å spørre en database om ledige enheter mellom to forskjellige datoer.

 

Tabellene ser slik ut:

TABLE `Facilities` (
 `id` int(11) NOT NULL auto_increment,
 `name` varchar(150) NOT NULL,
 `price` double NOT NULL,
 `description` text NOT NULL,
 PRIMARY KEY  (`id`)
)

TABLE `Reservations` (
 `id` int(11) NOT NULL auto_increment,
 `customer_id` int(11) NOT NULL,
 `facility_id` int(11) NOT NULL,
 `reservation_from` datetime NOT NULL,
 `reservation_to` datetime NOT NULL,
 PRIMARY KEY  (`id`)
)

 

Jeg ønsker å utforme en spørring som sender inn to parametere "ankomstdato" og "avreisedato" på bakgrunn av dette vil jeg ha tilbake alle enheter som er ledige innenfor denne perioden.

 

Ønsker også tips om tabellene kan utformes anderledes. Hvis det er til hjelp så bruker jeg MYSQL 5.

Endret 15. juni 2008 av KurtGandalf

[blackbrrd]

SELECT *
FROM Facilities WHERE NOT EXISTS (
SELECT 1 
FROM Reservations WHERE Facilities.id = facility_id AND ((reservation_from BETWEEN x AND y) OR (reservation_to BETWEEN x AND y))
)

Antar du er ute etter ledige enheter i periode, bytt x med fra og med og y med til og med datoen i perioden.

 

Går vel strengt tatt an å gjøre med en left join også...

SELECT *
FROM Facilities LEFT JOIN Reservations ON Facilities.id = facility_id AND ((reservation_from BETWEEN x AND y) OR (reservation_to BETWEEN x AND y))
WHERE Reservations IS NULL

Er ikke helt sikker på om den siste spørringen er riktig, er alltid litt vanskligere å formulere seg med LEFT JOIN og IS NULL enn med NOT EXISTS, men query optimizeren gjør gjerne en bedre jobb med LEFT JOINs.

Endret 15. juni 2008 av blackbrrd

[Thullball]

Takk skal du ha, har ikke prøvd INNER JOIN spørringen ennå, men den første fungerte utmerket!

[Thullball]

Fant ut av INNER JOIN spørringen:

 

WHERE isNull(Reservations.id)

[Thullball]

Problemet med denne spørringen kommer når man har en reservasjon eksempel:

 

Fra 05.05.05

Til 10.05.05

 

og deretter søker etter ledige lokaler mellom disse datoene eks.:

 

Fra 07.05.06

Til 08.05.06

 

ved dette søket så returneres det en rad som ledig selv om den faktisk er reservert.

Finnes det noen god måte å komme rundt dette?

[Thullball]

Tror denne løste problemet, hvis noen vet om bedre eller optimaliserte måter så si i fra!

 

SELECT *
FROM Facilities LEFT JOIN Reservations ON Facilities.id = facility_id AND ((reservation_from BETWEEN DATE('2008-06-18 14:00:00') AND DATE('2008-06-19 12:00:00')) OR (reservation_to BETWEEN DATE('2008-06-18 14:00:00') AND DATE('2008-06-19 12:00:00')))
OR (
(DATE('2008-06-18 14:00:00') BETWEEN reservation_from AND reservation_to)
)
OR (
(DATE('2008-06-19 12:00:00') BETWEEN reservation_from AND reservation_to)
)
WHERE isNull(Reservations.id)

[Thullball]

Huff... blir en lang tråd!

 

Nå har jeg forsøkt å legge inn en ny enhet (Facility) og ikke lagt inn noen reservasjoner på denne.

 

Utførte noen tester:

 

Reservasjon på enhet 1 (ingen reservasjoner på enhet 2):

Fra: 05.05.05 Til: 08.05.05

 

Søk 1:

Fra 03.05.05 - Til 04.05.05 (Returnerte 2 rader - OK)

 

Søk 2:

Fra 03.05.05 - Til 06.05.05 (Returnerte 0 rader - Feil)

Her skulle det blitt returnert 1 rad med "Enhet 2" ettersom den ikke har noen reservasjoner.

 

Noen som ser noe feil med spørringen som gjør at dette skjer?

[Thullball]

Det hele viste seg å være veldig enkelt..

 

SELECT * FROM Facilities LEFT JOIN Reservations ON Facilities.id = facility_id WHERE reservation_to <= #from_date# OR  reservation_from >= #to_date#