在SQLalchemy中的左联接上过滤 [英] Filtering on a left join in SQLalchemy
问题描述
我要使用SQLalchemy执行左外部联接,并过滤掉联接表中确实有匹配项的行.
Using SQLalchemy I want to perform a left outer join and filter out rows that DO have a match in the joined table.
我正在发送推送通知,所以我有一个Notification
表.这意味着我还有一个ExpiredDeviceId
表来存储不再有效的device_id. (我不想删除受影响的通知,因为用户以后可能会重新安装该应用,此时通知应根据Apple的文档恢复.)
I'm sending push notifications, so I have a Notification
table. This means I also have a ExpiredDeviceId
table to store device_ids that are no longer valid. (I don't want to just delete the affected notifications as the user might later re-install the app, at which point the notifications should resume according to Apple's docs.)
CREATE TABLE Notification (device_id TEXT, time DATETIME);
CREATE TABLE ExpiredDeviceId (device_id TEXT PRIMARY KEY, expiration_time DATETIME);
注意:每个device_id可能有多个通知.每个设备没有设备"表.
因此,当执行SELECT FROM Notification
时,我应该进行相应的过滤.我可以用SQL做到:
So when doing SELECT FROM Notification
I should filter accordingly. I can do it in SQL:
SELECT * FROM Notification
LEFT OUTER JOIN ExpiredDeviceId
ON Notification.device_id = ExpiredDeviceId.device_id
WHERE expiration_time IS NULL
但是我如何在SQLalchemy中做到这一点?
But how can I do it in SQLalchemy?
sess.query(
Notification,
ExpiredDeviceId
).outerjoin(
(ExpiredDeviceId, Notification.device_id == ExpiredDeviceId.device_id)
).filter(
???
)
或者,我可以使用device_id NOT IN (SELECT device_id FROM ExpiredDeviceId)
子句来执行此操作,但这似乎效率较低.
Alternately I could do this with a device_id NOT IN (SELECT device_id FROM ExpiredDeviceId)
clause, but that seems way less efficient.
推荐答案
是否需要在元组中拉回ExpiredDeviceId?如果您不这样做(即,您只是关心实时的device_ids),那么您就不能这样做:
Do you need to be pulling back the ExpiredDeviceId in a tuple? If you don't (i.e. you just care about live device_ids), then can't you just do:
sess.query(
Notification
).outerjoin(
(ExpiredDeviceId, Notification.device_id == ExpiredDeviceId.device_id)
).filter(
ExpiredDeviceId.expiration_time == None
)
这篇关于在SQLalchemy中的左联接上过滤的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!