I can't find any way to get SqlAlchemy to correlate a table referenced in a
computed column when the query has a union.
Simplified test case:
Base = declarative.declarative_base()
class MyTable(Base):
__tablename__ = 'my_table'
score = Column(Integer, primary_key=True)
my_alias = aliased(MyTable, name="my_alias")
As a silly query, suppose I wanted to list each score along with an
indicator of whether a higher score exists in the table. This works fine:
query_1 = Query(MyTable)
query_1 = query_1.add_column(Query(my_alias).filter(my_alias.score > MyTable
.score).exists())
and produces the query:
SELECT my_table.score AS my_table_score, EXISTS (SELECT 1
FROM my_table AS my_alias
WHERE my_alias.score > my_table.score) AS anon_1
FROM my_table
However, if the query is has a union:
query_2 = Query(MyTable).union(Query(MyTable))
query_2 = query_2.add_column(Query(my_alias).filter(my_alias.score > MyTable
.score).exists())
then SqlAlchemy refuses to correlate the MyTable in the added column with
the MyTable in the outer query:
SELECT anon_1.my_table_score AS anon_1_my_table_score, EXISTS (SELECT 1
FROM my_table AS my_alias, my_table -- How do I get rid of the second
my_table here?
WHERE my_alias.score > my_table.score) AS anon_2
FROM (SELECT my_table.score AS my_table_score
FROM my_table UNION SELECT my_table.score AS my_table_score
FROM my_table) AS anon_1
I tried adding an explicit correlate(), but it does not help:
query_2 = query_2.add_column(Query(my_alias).filter(my_alias.score > MyTable
.score).correlate(MyTable).exists())
How can I force the MyTable in the added column to correlate with the outer
query?
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
