似乎您正在寻找任何基于提供的开始/结束日期没有保留冲突的“对象”。进行coalcece()以始终包含保留中从未找到的那些对象是一个不错的选择,但是,作为左联接,我将尝试在找到日期的地方进行左联接,并忽略所有找到的对象。就像是
SELECT DISTINCT
o.id
FROM
objects o
LEFT JOIN reservations r
ON o.id = r.object_id
AND ( r.from_date between '2012-04-05' and '2012-04-08'
OR r.to_date between '2012-04-05' and '2012-04-08' )
WHERE
o.location_id = 201
AND r.object_id IS NULL
LIMIT 20
我将确保通过(object_id,from_date)和另一个(object_id,to_date)在保留表上建立索引。通过显式地使用范围之间的from_date(以及迄今为止),您正在专门寻找占用此时间段的预留空间。如果找到它们,则不允许这样做,因此WHERE子句将查找“ r.object_id IS NULL”(即:在您提供的日期范围内未发现任何冲突)
从我先前的答案扩展,并通过在(id,from date)和(id,to date)上有两个不同的索引,您可以通过分别加入每个索引的保留并期望两个保留集中的NULL来获得更好的性能…
SELECT DISTINCT
o.id
FROM
objects o
LEFT JOIN reservations r
ON o.id = r.object_id
AND r.from_date between '2012-04-05' and '2012-04-08'
LEFT JOIN reservations r2
ON o.id = r2.object_id
AND r2.to_date between '2012-04-05' and '2012-04-08'
WHERE
o.location_id = 201
AND r.object_id IS NULL
AND r2.object_id IS NULL
LIMIT 20