I’m trying to do a WHERE EXISTS
clause in SQLAlchemy. The SQL looks like this:
SELECT id, title FROM package AS package1 WHERE EXISTS ( SELECT title FROM package as package2 WHERE package1.id <> package2.id AND package1.title = package2.title ) ORDER BY title DESC;
Is it possible to do this in SQLAlchemy without using raw SQL? I’ve tried this but it doesn’t seem to be returning the correct results:
t = model.Session.query( model.Package.id, model.Package.title ).subquery('t') package = model.Session.query( model.Package.title ).filter( model.Package.id != t.c.id, model.Package.title == t.c.title )
Advertisement
Answer
Use sqlalchemy.orm.aliased() and exists():
from sqlalchemy.orm import aliased package_alias = aliased(Package) session.query(Package.id, Package.title) .filter(session.query(package_alias.title) .filter(Package.id != package_alias.id) .filter(Package.title == package_alias.title) .exists()) .order_by(Package.title.desc())