Filtering on a left join in SQLalchemy

Question

Using SQLalchemy I want to perform a left outer join and filter out rows that DO have a match in the joined table.

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

Note: there may be multiple Notifications per device_id. There is no "Device" table for each device.

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

But how can I do it in SQLalchemy?

sess.query(
    Notification, 
    ExpiredDeviceId
).outerjoin(
    (ExpiredDeviceId, Notification.device_id == ExpiredDeviceId.device_id)
).filter(
    ???
)

Alternately I could do this with a device_id NOT IN (SELECT device_id FROM ExpiredDeviceId) clause, but that seems way less efficient.

Answer 1

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
)