如何在PostgreSQL中为顶层父表设置Where条件筛选可用公寓

你的理解是不正确的，原有查询的逻辑和你预期的需求并不匹配。

你使用的默认JOIN本质是内连接，同时你原查询中appointments.aparmentId存在拼写错误（少了字母t，正确为apartmentId）也会影响结果。正常执行的情况下逻辑是先将公寓表和预约表按apartmentId关联，得到所有「公寓+对应预约」的组合行，再通过WHERE条件过滤掉其中预约时间和目标范围重叠的行，最终返回的是所有非重叠的「公寓+预约」组合。只要某套公寓存在任意一条不重叠的预约记录，就会出现在结果里，完全无法实现「指定时间段内完全无预约」的筛选要求。

以下是两种符合需求的实现方案：

方案1：使用NOT EXISTS（最推荐，逻辑直观性能好） #

SELECT * FROM apartments a
WHERE NOT EXISTS (
    SELECT 1 FROM appointments app
    WHERE 
        app.apartmentId = a.id
        AND app.datesReserved && '[2020-12-15T15:00:00.000Z, 2020-12-17T16:00:00.000Z)'::tstzrange
);

逻辑说明：直接筛选所有「不存在任何和目标时间段重叠的预约记录」的公寓，完全匹配你的需求。

方案2：使用左连接+空值判断 #

SELECT DISTINCT a.* FROM apartments a
LEFT JOIN appointments app 
    ON app.apartmentId = a.id
    AND app.datesReserved && '[2020-12-15T15:00:00.000Z, 2020-12-17T16:00:00.000Z)'::tstzrange
WHERE app.id IS NULL;

注意事项：

• 时间重叠的判断条件必须写在LEFT JOIN的ON子句中，若写在WHERE子句中会让左连接退化为内连接，达不到筛选效果
• 增加DISTINCT是为了避免同一套公寓存在多条非重叠预约时，返回重复的公寓数据

内容的提问来源于stack exchange，提问作者Rachael O'James