Hello I tried the use of where(e) but it fires the following error: AttributeError: 'Query' object has no attribute 'where'
I tried with filter(e) instead and the query does not fire any error but the result is not exactly what is expected. Here is the query: e = session.query(Plant).\ join(Article, Plant.articles).\ join(Catalogitem, Article.catalogitems).\ filter(Catalogitem.marketingseason == marketingseason).\ exists() taxon = session.query(Taxon).filter(e) Here is the SQL output: 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 ) As we can see, the subquery is not correlated to the enclosing query via the relationship Taxon.plant (the "AND botany.plant.taxon_id = botany.taxon.id" is missing in the subquery). Is it possible to do that? I join a new file (example2.py) with the new query. Le mercredi 7 août 2013 20:56:43 UTC+2, Michael Bayer a écrit : > > > On Aug 7, 2013, at 11:58 AM, Etienne Rouxel > <rouxel....@gmail.com<javascript:>> > wrote: > > 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? > > > > Query has an exists() method that will turn the SELECT you've constructed > into an EXISTS: > http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html?highlight=query.exists#sqlalchemy.orm.query.Query.exists > So you say "e = query(Plant).join(..).join(..).filter(...).exists(); > query(Taxon).where(e)". > > before we had that method you also could construct the joins using > orm.join() and then use sqlalchemy.exists() to produce a select, but the > Query method is a shortcut on that. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
# -*- 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__': engine = create_engine('postgresql://nurseryacme_employee@localhost:5432/nurseryacme') Session = sessionmaker(bind=engine) session = Session() marketingseason = Marketingseason(id= -2147483648) e = session.query(Plant).\ join(Article, Plant.articles).\ join(Catalogitem, Article.catalogitems).\ filter(Catalogitem.marketingseason == marketingseason).\ exists() # The use of where(e) fires the following error # AttributeError: 'Query' object has no attribute 'where' # taxon = session.query(Taxon).where(e) # The use of filter(e) instead is fine but the result is not exactly what is # expected. Indeed, the subquery is not correlated via the relationship Taxon.plant. taxon = session.query(Taxon).filter(e) print taxon # SQL output: # # 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 # )