there's a lot there, here's a proof of concept: from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base() class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) data = Column(String) sess = Session() cte_1 = sess.query(A).cte('c1') cte_2 = sess.query(A).cte('c2') stmt = union(cte_1.select(), cte_2.select()) print sess.query(A).select_entity_from(stmt) if we want to filter, making an alias() out of stmt then using that works: stmt = union(cte_1.select(), cte_2.select()).alias() print sess.query(A).select_entity_from(stmt).filter(stmt.c.data == 'hi') output: WITH c1 AS (SELECT a.id AS id, a.data AS data FROM a), c2 AS (SELECT a.id AS id, a.data AS data FROM a) SELECT anon_1.id AS anon_1_id, anon_1.data AS anon_1_data FROM (SELECT c1.id AS id, c1.data AS data FROM c1 UNION SELECT c2.id AS id, c2.data AS data FROM c2) AS anon_1 WHERE anon_1.data = :data_1 On Sep 25, 2014, at 9:12 PM, Jonathan Vanasco <jvana...@gmail.com> wrote: > This is the test case I've been working on today. > > https://gist.github.com/jvanasco/9be1f528526e496fc751 > > The target sql looks crappy, because it's just an example. > > the various issues have been: > > * if i can build a query without an Exception: > ** I duplicate the query within a subqueries > ** I build it in a way that I don't have addressable columns (for > sorting/joins/etc) > > * if i raise an exception (more often), it's because I'm not able to turn a > union/cte into the correct type of object (which has addressable columns, etc) > > > -- > 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 http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.