[sqlalchemy] How to make a SQLAlchemy query using exists with a correlated subquery containing joins?

[Etienne Rouxel]

Hello

Long story cut short, I would like to know if it is possible to generate
a query with SQLAlchemy ORM such as the one below and how.

    SELECT botany.taxon.id AS botany_taxon_id
    FROM botany.taxon
    WHERE EXISTS (SELECT 1
        FROM botany.plant
        JOIN product.article ON botany.plant.id = product.article.plant_id
        JOIN catalog.catalogitem ON product.article.plant_id =
catalog.catalogitem.plant_id AND product.article.article_id =
catalog.catalogitem.article_id
        WHERE :param_1 = catalog.catalogitem.marketingseason_id
        AND botany.plant.taxon_id = botany.taxon.id
    )

Put differently, it is like the regular use of EXISTS
(http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#using-exists)
but with a more complex subquery that contains JOINs.

Is it possible to do such a query?

If it can help, I have joined a file (example.py) with all the mapped
classes used for the example above and also with a different query that
should returns the same results as the one I would like to build here.

-- 
Etienne Rouxel

# -*- coding: utf-8 -*-
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

_taxon_table = Table('taxon', Base.metadata,
        Column('id', Integer, primary_key=True),
        schema='botany'
    )

_plant_table = Table('plant', Base.metadata,
        Column('id', Integer, primary_key=True),
        Column('taxon_id', Integer),
        ForeignKeyConstraint(['taxon_id'], ['botany.taxon.id']),
        schema='botany'
    )

_article_table = Table('article', Base.metadata,
        Column('plant_id', Integer, primary_key=True),
        Column('article_id', Integer, primary_key=True),
        ForeignKeyConstraint(['plant_id'], ['botany.plant.id']),
        schema='product'
    )

_marketingseason_table = Table('marketingseason', Base.metadata,
        Column('id', Integer, primary_key=True),
        schema='catalog'
    )

_catalogitem_table = Table('catalogitem', Base.metadata,
        Column('plant_id', Integer, primary_key=True),
        Column('article_id', Integer, primary_key=True),
        Column('marketingseason_id', Integer, primary_key=True),
        ForeignKeyConstraint(
            ['plant_id', 'article_id'],
            ['product.article.plant_id', 'product.article.article_id']),
        ForeignKeyConstraint(['marketingseason_id'], ['catalog.marketingseason.id']),
        schema='catalog'
    )

class Taxon(Base):
    __table__ = _taxon_table

class Plant(Base):
    __table__ = _plant_table

    taxon = relationship('Taxon',
                innerjoin=True,
                backref=backref('plant',
                    uselist=False))

class Article(Base):
    __table__ = _article_table

    plant = relationship('Plant',
                innerjoin=True,
                backref=backref('articles',
                    cascade='all, delete-orphan'))

class Marketingseason(Base):
    __table__ = _marketingseason_table

class Catalogitem(Base):
    __table__ = _catalogitem_table

    article = relationship('Article',
                innerjoin=True,
                backref=backref('catalogitems',
                    cascade='all, delete-orphan'))

    marketingseason = relationship('Marketingseason',
                innerjoin=True,
                backref=backref('catalogitems'))

if __name__ == '__main__':

    # Initialize database models
    engine = create_engine('postgresql://nurseryacme_employee@localhost:5432/nurseryacme')
    Session = sessionmaker(bind=engine)
    session = Session()

    marketingseason = Marketingseason(id= -2147483648)

    taxon = session.query(Taxon).\
        filter(
            Taxon.plant.has(
                Plant.articles.any(
                    Article.catalogitems.any(
                        Catalogitem.marketingseason == marketingseason))))

    print taxon
    # SQL output:
    #
    # SELECT botany.taxon.id AS botany_taxon_id
    # FROM botany.taxon
    # WHERE EXISTS (SELECT 1
    # FROM botany.plant
    # WHERE botany.taxon.id = botany.plant.taxon_id AND (EXISTS (SELECT 1
    # FROM product.article
    # WHERE botany.plant.id = product.article.plant_id AND (EXISTS (SELECT 1
    # FROM catalog.catalogitem
    # WHERE product.article.plant_id = catalog.catalogitem.plant_id AND product.article.article_id = catalog.catalogitem.article_id AND :param_1 = catalog.catalogitem.marketingseason_id)))))

signature.asc
Description: OpenPGP digital signature