I need to query multiple entities, something like session.query(Entity1, Entity2)
, only from a subquery rather than directly from the tables. The docs have something about selecting one entity from a subquery but I can’t find how to select more than one, either in the docs or by experimentation.
My use case is that I need to filter the tables underlying the mapped classes by a window function, which in PostgreSQL can only be done in a subquery or CTE.
EDIT: The subquery spans a JOIN of both tables so I can’t just do aliased(Entity1, subquery)
.
Advertisement
Answer
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) bs = relationship("B") class B(Base): __tablename__ = "b" id = Column(Integer, primary_key=True) a_id = Column(Integer, ForeignKey('a.id')) e = create_engine("sqlite://", echo=True) Base.metadata.create_all(e) s = Session(e) s.add_all([A(bs=[B(), B()]), A(bs=[B()])]) s.commit() # with_labels() here is to disambiguate A.id and B.id. # without it, you'd see a warning # "Column 'id' on table being replaced by another column with the same key." subq = s.query(A, B).join(A.bs).with_labels().subquery() # method 1 - select_from() print s.query(A, B).select_from(subq).all() # method 2 - alias them both. "subq" renders # once because FROM objects render based on object # identity. a_alias = aliased(A, subq) b_alias = aliased(B, subq) print s.query(a_alias, b_alias).all()