Hello. This is the correct solution. I think you HAVE to specify the correlation between the exists() subquery and the main query. But I am no SA expert.
I would suggest to add id (a sequence) as your primary key and use it as FK everywhere. You can turn the current composite primary key into a composite UNIQUE constraint to preserve the integrity check. HTH, Ladislav Lenart On 8.8.2013 09:25, Etienne Rouxel wrote: > I have managed to add the missing SQL part with the following query: > > e = session.query(Plant).\ > join(Article, Plant.articles).\ > join(Catalogitem, Article.catalogitems).\ > filter( > Catalogitem.marketingseason == marketingseason, > Plant.taxon_id == Taxon.id > ).\ > exists() > > taxon = session.query(Taxon).filter(e) > > > However, I would like (if possible) to fully take advantage of SQLAlchemy and > avoid writing the test with the columns explicitly. Indeed, I have composite > primary keys with 4 columns in some of my other real case scenario so that > would > be great if I could say something like: Plant.taxon == Taxon of the enclosing > query. > > Le jeudi 8 août 2013 09:02:34 UTC+2, Etienne Rouxel a écrit : > > 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 <http://botany.taxon.id> AS botany_taxon_id > FROM botany.taxon > WHERE EXISTS (SELECT 1 > FROM botany.plant > JOIN product.article ON botany.plant.id <http://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 > <http://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> > 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 <http://botany.taxon.id> AS >> botany_taxon_id >> FROM botany.taxon >> WHERE EXISTS (SELECT 1 >> FROM botany.plant >> JOIN product.article ON botany.plant.id >> <http://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 >> <http://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 >> >> <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 > > <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. -- 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.
