如何仅返回用户输入的日期范围内没有现有预订的属性的ID？

Question

I'm setting up a property search on a vacation rental multi-property site. The search takes parameters for arrival date and departure date. The properties have multiple duplicate dates, since they're allowed to import multiple icals for each property to integrate bookings. How can I write a query that returns ONLY property IDs that do not have existing booked dates in reference to the user input?

Essentially, I'd like to return all properties that are available during the date range from user input.

Tried this.

SELECT p.name 
FROM property AS p
INNER JOIN property_calendar AS pc
ON p.id = pc.property_id
WHERE COALESCE('2018-11-17' NOT BETWEEN pc.start AND pc.end, TRUE)
      AND COALESCE('2018-11-19' NOT BETWEEN pc.start AND pc.end, TRUE)

DB Tables: (only showing relevant columns)

property

id | name 
-------------------------------------
1  | Beaches & Flows
2  | Mo Beaches Mo Problems
3  | 99 Problems and they all Beaches

property_calendar

id | property_id | start (Y-m-d) | end (Y-m-d) 
----------------------------------------------
1  | 1           | 2019-3-13     | 2019-3-17
2  | 1           | 2019-4-13     | 2019-4-17
3  | 1           | 2019-3-13     | 2019-3-17
4  | 1           | 2019-3-13     | 2019-3-17
5  | 2           | 2019-3-13     | 2019-3-17
6  | 3           | 2019-5-13     | 2019-5-17
7  | 3           | 2019-6-13     | 2019-6-17
8  | 3           | 2019-7-13     | 2019-7-17

Though there are duplicate dates for each property on the calendar table, I would only like it to return a property ID only once if it is available in that date range. If there are ANY occurrences where the date is not available for a property, then nothing should be returned in the query.

I really really appreciate any of the help I can get!

Answer 1

Check for existence of a date range overlap with a NOT EXISTS subquery:

set @new_start = '2019-06-16';
set @new_end   = '2019-06-19';

select *
from property p
where not exists (
  select *
  from property_calendar c
  where c.property_id = p.id
    and c.start < @new_end
    and c.end   > @new_start
)

Result:

id | name
---|-----------------------
1  | Beaches & Flows
2  | Mo Beaches Mo Problems

Property 3 is excluded, because the range (2019-6-13, 2019-6-17) overlaps with (2019-06-16, 2019-06-19).

Dependent on if you want to allow an overlap in a single day, you might need to change < and > to <= and >=.

Demo