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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
