How to make a SQLAlchemy query using exists with a correlated subquery containing joins?

[Etienne Rouxel]

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?

If it can help, I have joined a file (example.py) with all the mapped classes used for the example above and also with a different query that should returns the same results as the one I would like to build here.

--
Etienne Rouxel

[Michael Bayer]

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

[Etienne Rouxel]

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.

[Etienne Rouxel]

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.

[Ladislav Lenart]

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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.

[Michael Bayer]

On Aug 8, 2013, at 2:02 AM, Etienne Rouxel <rouxel....@gmail.com> 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 <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 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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

<example2.py>

[Etienne Rouxel]

Ok, thank you very much, one more time!

And thank you to Ladislav Lenart as well.