SQLAlchemy NOT exists on subselect?

Question

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!

Answer 1

You're querying the FROM images table, but the WHERE clause is a subquery, not e.image_id=i.id AND e.chat_id=:chat_id (these filters are for events instead). So, the correct query is of the form

session.query(Image).filter(subquery).order_by(func.random()).limit(1)

The way to form an EXISTS subquery is with the .exists() method, so to get NOT EXISTS just use the ~ operator:

subquery = ~session.query(Event).filter(Event.image_id == Image.id, Event.chat_id == chat_id).exists()

Note that the emitted query is not identical to your original (e.g. it uses EXISTS (SELECT 1 ...)), but it's functionally the same.