On Aug 8, 2013, at 2:02 AM, Etienne Rouxel <[email protected]> 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].
> 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.
>
>
> <example2.py>
signature.asc
Description: Message signed with OpenPGP using GPGMail
