Help with resevation query

MgM Web · #1 (**permalink**) 06-15-08, 15:00

Im trying to develop a booking system and need help with the basic "available units" query.

My tables:

Code:

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`)
)

I would like to make a query that accepts 2 parameters "from date" and "to date". Based on these dates I need my query to return all available units/facilities within this period.

Please any help is apreciated, if anyone gor improvements to my tables aswell dont hold back on informing me.

MgM Web · #2 (**permalink**) 06-15-08, 16:48

Code:

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 isNull(Reservations.id)

r937 · #3 (**permalink**) 06-15-08, 18:02

if reservation_from is less than x and reservation_to is greater than y, the facility is booked for a larger range than x to y, and is unavailable

but your query would return it

MgM Web · #4 (**permalink**) 06-15-08, 18:34

Hmm.. I just tested it and it does not return any rows when the search range is greater than the reserved range. Think it is working!

But I would love to hear your solution as well.

r937 · #5 (**permalink**) 06-15-08, 20:10

your search range is x to y?

then a reservation which starts before x and ends after y is definitely not available, and yet your query returns it

MgM Web · #6 (**permalink**) 06-15-08, 20:46

Yes, you are right! I ahd to alter the query into this:

Code:

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)

But now my problem is adding multiple facilities.

Done some tests:

Reservation on facility 1 (no reservations on facility 2):
From: 05.05.2005 To: 08.05.2005

Search 1:
From 03.05.05 - To 04.05.05 (Returns 2 rows - OK)

Search 2:
From 03.05.05 - To 06.05.05 (Returns 0 rows - Wrong)
Should have returned 1 row with "Facility 2" since there are no reservations on "Facility 2" yet.

Have I missed something?

r937 · #7 (**permalink**) 06-15-08, 21:00

have a look at this --
Absences between a date range...

if anything, at least the timeline diagram should help you

MgM Web · #8 (**permalink**) 06-16-08, 03:43

Think I got it figured out now, the timeline did help!

Code:

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

Clean and easy, thank you for your help.

r937 · #9 (**permalink**) 06-16-08, 05:49

should be an INNER JOIN, by the way

MgM Web · #10 (**permalink**) 06-21-08, 06:29

I have run into a problem with my query, until now I have tested with just 2 rows in my reservation table:

ID: 1
From: 2008-06-17
To: 2008-06-20
Facility: 1

ID: 2
From: 2008-06-24
To: 2008-06-26
Facility: 2

My query works alright with just these 2 rows. The problem occurs when there are more than 2 rows in the reservation table.

If I add another reservation:

ID: 3
From: 2008-06-28
To: 2008-06-30
Facility: 1

And run my query with parameters "FROM 2008-06-22, TO 2008-06-23" i get this result:

Facility (1, 2, 1)

But what I wanted to see was just 1 and 2.

Same happens when i query with "FROM 2008-06-28, TO 2008-06-30" i get this result:

Facility (1, 2)

But I expected only 2.

r937 · #11 (**permalink**) 06-21-08, 06:49

could you show your query?

MgM Web · #12 (**permalink**) 06-21-08, 06:51

Code:

SELECT *
FROM Facilities
INNER JOIN Reservations ON Facilities.id = facility_id
WHERE reservation_to <= '2008-06-28 14:00:00'
OR reservation_from >= '2008-06-30 12:00:00'

Tables are still:

Code:

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`)
)

r937 · #13 (**permalink**) 06-21-08, 07:31

your query is incorrect

change this --

Code:

WHERE reservation_to <= '2008-06-28 14:00:00'
OR reservation_from >= '2008-06-30 12:00:00'

to this --

Code:

WHERE reservation_to >= '2008-06-28 14:00:00'
AND reservation_from <= '2008-06-30 12:00:00'

MgM Web · #14 (**permalink**) 06-21-08, 10:13

Ok, thank you.

Now I get the occupied facilities on a given date.

That is ok, but is there a clever way to get the available facilities or do I have to make another query on the facilities and loop through all facilities and only show the available ones like this:

Query 1, on the given date facility 1 is occupied and the query return:
1

Query 2, return all facilities:
1
2

Loop and remove even id's will return:
2

But are there better ways to do the same?

r937 · #15 (**permalink**) 06-21-08, 10:31

yes, you can do it in one query

this query gives you all the available facilities in the requested time preiod:

Code:

SELECT f.id
     , f.name
     , f.price
     , f.description
  FROM Facilities AS f
LEFT OUTER
  JOIN ( SELECT DISTINCT facility_id
           FROM Reservations 
          WHERE reservation_to   >= '2008-06-28 14:00:00' 
            AND reservation_from <= '2008-06-30 12:00:00'
       ) as r
    ON r.facility_id = f.id
 WHERE r.facility_id IS NULL

Internet Services

Web Development

Digital Marketing

Consumer Tech