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 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 <[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.
