Filtering on a left join in SQLalchemy
Posted
by
Adam Ernst
on Stack Overflow
See other posts from Stack Overflow
or by Adam Ernst
Published on 2011-01-07T19:55:42Z
Indexed on
2011/01/10
12:54 UTC
Read the original article
Hit count: 285
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 == 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.
© Stack Overflow or respective owner