Skip to content
Advertisement

How to do a where exists in nested query with SQLAlchemy?

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())
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement