SQLAlchemy NOT exists on subselect?

Viewed 4466

I'm trying to replicate this raw sql into proper sqlalchemy implementation but after a lot of tries I can't find a proper way to do it:

SELECT *
FROM images i
WHERE NOT EXISTS (
    SELECT image_id
    FROM events e
    WHERE e.image_id=i.id AND e.chat_id=:chat_id)
ORDER BY random()
LIMIT 1

Closest I got is:

session.query(Image).filter(and_(Event.image_id == Image.id, Event.chat_id == chat_id)).order_by(func.random()).limit(1)

But I cant seem to find how to put the NOT EXISTS clause.

Can anyone lend a helping hand?

Thanks!

1 Answers
Related