iT邦幫忙

0

SQLAlchemy下查詢,兩層一對多

這是簡化版的模型,當然Room與Bed還有很多屬性可作查詢,在此先省略
我要找的條件是:有空位且可用的房間
範例:在此條件下,Room2會被篩選出來,因為他擁有至少一床是可用且當前未住人(床3的王小明已經退房了)
https://ithelp.ithome.com.tw/upload/images/20181204/20088395zjwR1mc6xf.jpg
我下的指令如下,只是結果不如預期...有人知道如何撰寫嗎

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()

圖:
https://ithelp.ithome.com.tw/upload/images/20181203/20088395CCVbLodUse.png

有資料內容可以加入舉例嗎??
例如:原始內容→預期內容
黃彥儒 iT邦好手 1 級 ‧ 2018-12-04 11:23:43 檢舉
有的,已經附上

2 個回答

1
純真的人
iT邦高手 1 級 ‧ 2018-12-04 13:42:45
最佳解答

你是卡在exists判斷有問題~
我用MSSQL跑了一下~
你改用between去算使用區間就應該對了~

declare @bed table(
	bid int
	,name nvarchar(8)
	,room_id int
	,[enable] int
)

declare @bed_history table(
	hid int
	,bed_id int
	,c_time datetime
	,d_time datetime
	,[user] nvarchar(8)
)

declare @room table(
	rid int
	,builder nvarchar(8)
)

insert into @room
values(1,'一宿A001')
,(2,'一宿A002')

insert into @bed
values(1,'A床',1,0)
,(2,'B床',1,1)
,(3,'A床',2,0)
,(4,'B床',2,0)

insert into @bed_history
values(1,1,'2018/10/4','2999/1/1','王大明')
,(2,3,'2018/11/1','2018/12/1','王小明')

SELECT rid AS room_rid
, builder AS room_builder
FROM @room 
INNER JOIN @bed ON rid = room_id 
INNER JOIN @bed_history ON bid = bed_id
WHERE (
	EXISTS (
		SELECT 1 
		FROM @bed 
		WHERE rid = room_id 
		AND enable = 0
	)
) 
AND NOT (
	EXISTS (
		SELECT 1
		FROM @bed_history 
		WHERE bid = bed_id 
		AND Convert(date,GetDate()) between c_time and d_time 
	)
) 

所以你的應該是這樣

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 (SELECT 1
FROM bed_history 
WHERE bed.bid = bed_history.bed_id 
AND %(c_time_1)s between bed_history.c_time and bed_history.d_time
)) 
看更多先前的回應...收起先前的回應...
黃彥儒 iT邦好手 1 級 ‧ 2018-12-04 14:12:02 檢舉

了解,我嘗試把他翻回去試試看

黃彥儒 iT邦好手 1 級 ‧ 2018-12-08 14:02:35 檢舉

經過研究,將SQL翻譯回Python,只要將兩個條件合而為一即可

ava_room = ava_room.filter(~Bed.histories.any(and_(BedHistory.c_time<= datetime.datetime.now(), BedHistory.d_time >= datetime.datetime.now())))

謝謝您的回答,真的幫了很大的忙。

嗯嗯~~加油!

黃彥儒 iT邦好手 1 級 ‧ 2018-12-08 14:47:56 檢舉

ㄚㄚ...想說應該對,結果還是沒撈出來QQ

畢竟不是SQL是Python語言..
要試那一種可行吧...

黃彥儒 iT邦好手 1 級 ‧ 2018-12-09 13:00:18 檢舉

終於...確定能跑了

act_record = db.session.query(BedHistory.bed_id).filter(BedHistory.c_time <= datetime.datetime.now(),BedHistory.d_time >= datetime.datetime.now()).subquery()

free_bed = db.session.query(Bed.room_id).filter(~Bed.bid.in_(act_record)).subquery()

free_room = db.session.query(Room).filter(Room.rid.in_(free_bed)).all()

贊唷~~

1
rogeryao
iT邦研究生 3 級 ‧ 2018-12-04 14:33:45

MSSQL:

select room.*,bed.*
from room
left join bed on bed.room_id=room.rid
left join bed_history on bed_history.bed_id=bed.bid
where 1=1
and bed.enable=1
and bed_history.d_time is not null
and CONVERT(varchar(12), bed_history.d_time, 111)<CONVERT(varchar(12), GETDATE(), 111)

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=249a47fbbc739fba4482599a08420601

黃彥儒 iT邦好手 1 級 ‧ 2018-12-08 14:01:09 檢舉

因為純真的人比較詳細,所以我就選他為最佳解答
也很感謝您回答我的問題

我要發表回答

立即登入回答