Ok, thank you very much, one more time! And thank you to Ladislav Lenart as well.
Le jeudi 8 août 2013 15:33:58 UTC+2, Michael Bayer a écrit : > > > On Aug 8, 2013, at 2:02 AM, Etienne Rouxel > <[email protected]<javascript:>> > wrote: > > Hello > > I tried the use of where(e) but it fires the following error: > AttributeError: 'Query' object has no attribute 'where' > > > sorry, I meant "filter()" > > > > 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? > > > the solution you have where you've named "Plant.taxon_id == Taxon.id" is > the right approach. When your exists() is embedded into an enclosing > query, Taxon.id doesn't add "Taxon" to the local FROM clause and instead > knows to correlate to the enclosing query. > > 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. > > > I think that syntax should be added as a supported feature, however you > can get that now using this notation, since you just want the join > condition of Plant.taxon: > > e = session.query(Plant).\ > join(Article, Plant.articles).\ > join(Catalogitem, Article.catalogitems).\ > filter(Catalogitem.marketingseason == marketingseason).\ > filter(Plant.taxon.expression).\ > exists() > > > > > > > > > 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 <[email protected]> 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 [email protected] <javascript:>. > To post to this group, send email to [email protected]<javascript:> > . > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out. > > > <example2.py> > > > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
