StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL: Matching records that have x consecutive dates available between two dates
text
Body
copied!<p><strong>Background / Application</strong></p> <p>I have a MySQL database containing a table of rentable properties and a table of bookings for these properties. There is also a search feature for finding available properties between two provided dates. When searching, the user can enter the start date, the amount of days they wish to stay, and a date flexibility of up to +/- 7 days. A booking can start on the same day as another booking ends (party 1 leaves in the morning, party 2 arrives in the evening).</p> <p>I am having difficulty implementing the flexibility feature efficiently.</p> <p><strong>Schema</strong></p> <pre><code>CREATE TABLE IF NOT EXISTS `property` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE IF NOT EXISTS `property_booking` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `property_id` bigint(20) DEFAULT NULL, `name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL, `date_start` date DEFAULT NULL, `date_end` date DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; </code></pre> <p><strong>Sample Data</strong></p> <pre><code>INSERT INTO `property` (`name`) VALUES ('Property 1'), ('Property 2'), ('Property 3'); INSERT INTO `property_booking` (`property_id`,`name`,`date_start`,`date_end`) VALUES (1, 'Steve', '2011-03-01', '2011-03-08'), (2, 'Bob', '2011-03-13', '2011-03-20'), (3, 'Jim', '2011-03-16', '2011-03-23'); </code></pre> <p><strong>Sample Scenario</strong></p> <p>The user selects that they want to start their stay on 2011-03-10, they want to stay for 7 days, and they have a flexibility of +/- 2 days. I have compiled an image that visualises the data and parameters below. <em>(Red: Booking 1, Green: Booking 2, Stripes: Booking 3, Blue: Date range (2011-03-10, + 7 days and +/- 2 days flexibility))</em></p> <p><img src="https://i.stack.imgur.com/o9TUC.png" /></p> <p><strong>Expected Result</strong></p> <p>Property 1 <em>(Bookings available throughout date range)</em><br> Property 3 <em>(Bookings available starting on 2011-03-08 or 2011-03-09)</em></p> <p><strong>Current Method</strong></p> <p>My current query checks for overlap for all 7 day date ranges within the total searchable date range, like this:</p> <pre><code>SELECT p.`id`, p.`name` FROM `property` p WHERE (NOT (EXISTS (SELECT p2.`name` FROM `property_booking` p2 WHERE (p2.`property_id` = p.`id` AND '2011-03-10' < DATE_SUB(p2.`date_end`, INTERVAL 1 DAY) AND '2011-03-17' > DATE_ADD(p2.`date_start`, INTERVAL 1 DAY))))) OR (NOT (EXISTS (SELECT p3.`name` FROM `property_booking` p3 WHERE (p3.`property_id` = p.`id` AND '2011-03-11' < DATE_SUB(p3.`date_end`, INTERVAL 1 DAY) AND '2011-03-18' > DATE_ADD(p3.`date_start`, INTERVAL 1 DAY))))) OR (NOT (EXISTS (SELECT p4.`name` FROM `property_booking` p4 WHERE (p4.`property_id` = p.`id` AND '2011-03-09' < DATE_SUB(p4.`date_end`, INTERVAL 1 DAY) AND '2011-03-16' > DATE_ADD(p4.`date_start`, INTERVAL 1 DAY))))) OR (NOT (EXISTS (SELECT p5.`name` FROM `property_booking` p5 WHERE (p5.`property_id` = p.`id` AND '2011-03-12' < DATE_SUB(p5.`date_end`, INTERVAL 1 DAY) AND '2011-03-19' > DATE_ADD(p5.`date_start`, INTERVAL 1 DAY))))) OR (NOT (EXISTS (SELECT p6.`name` FROM `property_booking` p6 WHERE (p6.`property_id` = p.`id` AND '2011-03-08' < DATE_SUB(p6.`date_end`, INTERVAL 1 DAY) AND '2011-03-15' > DATE_ADD(p6.`date_start`, INTERVAL 1 DAY))))); </code></pre> <p>On the sample dataset, it's reasonably quick, but on much larger datasets it's going to get pretty sluggish, even more so when you build the full +/- 7 day flexibility.</p> <p>Does anyone have any suggestions as to how this query could be better written? </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload