It's really confusing. I ran into this a few months ago and wanted to stab things.
I can't answer your question, but I can push you in the right direction: The trick is in looking at the actual objects that are returned/expected by each item. Some operations will return objects that require a cte/select/subquery/etc call ; others will require an alias() or other call. The following example from one of my "experiments" should compile... class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) data = Column(String) # two different subquery sq_1 = sess.query(A.id.label('a_id')).subquery('sq1') sq_2 = sess.query(A.id.label('a_id')).subquery('sq2') # `union` generates a `sqlalchemy.sql.selectable.CompoundSelect` _union_compound_select = union(sq_1.select(), sq_2.select()) # but if we want to select against them... # this generates the right query _union_alias = _union_compound_select.alias('unioned') q = sess.query(_union_alias.c.a_id.label('a_id')).group_by(_union_alias.c.a_id,).order_by(sqlalchemy.desc('a_id')) # we want to order on the query's `a_id`, not the inner `_union_alias.a_id` # and then you can filter on it. filtered_1 = sess.query(B.a_id.label('a_id')).filter(B.id.in_((1,2,3))).subquery('filtered_1') q = q.outerjoin(filtered_1, _union_alias.c.a_id == filtered_1.c.a_id,) -- 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.