在SQLalchemy中的左联接上过滤

本文介绍了在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 )