On Sat, Jul 14, 2018 at 2:26 AM, 'David C' via sqlalchemy <sqlalchemy@googlegroups.com> wrote: > 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?
You actually need it to do more than that, since you will note that query.union() produces the UNION inside of a subquery called "anon_1". You actually want your subquery to refer to "anon_1", not "my_table", if you want to have it on the outside. Normally, when you use query.union(), you can still refer to MyTable on the outside, and the query applies adaptation such that "MyTable" is turned into "anon_1" so that it makes sense, such as if you said query_2.filter(MyTable.score > 5). But in the case of the EXISTS you're building, that's not working, and the reason is because of the behavior of the Query.exists() method specifically and I've proposed a change at https://bitbucket.org/zzzeek/sqlalchemy/issues/4304/why-does-querystatement-add. For now you can do the identical sequence by just using a Core exists construct which does not contain the "no traverse" annotation: from sqlalchemy import exists query_2 = s.query(MyTable).union(s.query(MyTable)) stmt1 = exists().where(my_alias.score > MyTable.score) query_2.add_column(stmt1) you'll note the resulting SQL finds all occurrences of "my_table" inside your subquery and replaces them appropriately with anon_1: SELECT anon_1.my_table_score AS anon_1_my_table_score, EXISTS (SELECT * FROM my_table AS my_alias WHERE my_alias.score > anon_1.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 > > -- > 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
