這是簡化版的模型，當然Room與Bed還有很多屬性可作查詢，在此先省略
我要找的條件是：有空位且可用的房間
範例：在此條件下，Room2會被篩選出來，因為他擁有至少一床是可用且當前未住人(床3的王小明已經退房了)

我下的指令如下，只是結果不如預期...有人知道如何撰寫嗎

ava_room = Room.query.join(Room.beds).join(Bed.histories)
ava_room = ava_room.filter(Room.beds.any(Bed.enable == True))
ava_room = ava_room.filter(~Bed.histories.any(BedHistory.c_time<= datetime.datetime.now()))
ava_room.filter(~Bed.histories.any(BedHistory.c_time >= datetime.datetime.now()))
res = ava_room.all()

翻譯出的SQL長這樣：

SELECT room.rid AS room_rid, room.builder AS room_builder
FROM room INNER JOIN bed ON room.rid = bed.room_id INNER JOIN bed_history ON bed.bid = bed_history.bed_id
WHERE (EXISTS (SELECT 1 
FROM bed 
WHERE room.rid = bed.room_id AND bed.enable = true)) AND NOT (EXISTS (SELECˊT 1
FROM bed_history 
WHERE bed.bid = bed_history.bed_id AND bed_history.c_time <= %(c_time_1)s)) AND NOT (EXISTS (SELECT 1 
FROM bed_history 
WHERE bed.bid = bed_history.bed_id AND bed_history.c_time >= %(c_time_2)s))

模型：

class Room(db.Model):
    rid = db.Column(db.Integer, primary_key=True, autoincrement=True)
    builder = db.Column(db.String(16), nullable=False)
    beds = db.relationship('Bed', back_populates='room')

    def __init__(self, builder: str):
        self.builder = builder


class Bed(db.Model):
    bid = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(8), nullable=False)
    room_id = db.Column(db.Integer, db.ForeignKey('room.rid'))
    room = db.relationship("Room", back_populates='beds')
    histories = db.relationship("BedHistory", back_populates='bed')
    enable = db.Column(db.Boolean, nullable=False)

    def __init__(self, room: Room, name: str):
        self.name = name
        self.room_id = room.rid
        self.enable = True


class BedHistory(db.Model):
    hid = db.Column(db.Integer, primary_key=True, autoincrement=True)
    bed_id = db.Column(db.Integer, db.ForeignKey('bed.bid'))
    bed = db.relationship('Bed', back_populates='histories')
    c_time = db.Column(db.DateTime, nullable=False)
    d_time = db.Column(db.DateTime, nullable=False)
    user = db.Column(db.String(4))

    def __init__(self, bed: Bed, name: str):
        self.user = name
        self.c_time = datetime.datetime.now()
        self.d_time = datetime.datetime.now() + datetime.timedelta(30)
        self.bed_id = bed.bid

    @hybrid_property
    def active(self):
        return self.c_time <= datetime.datetime.now() <= self.d_time

    def check_out(self):
        self.d_time = datetime.datetime.now()

圖：