SQLAlchemy ORM 从子查询中选择多个实体 [英] SQLAlchemy ORM select multiple entities from subquery
问题描述
我需要查询多个实体,例如 session.query(Entity1, Entity2)
,只能从子查询而不是直接从表中查询.文档中有关于 选择一个的内容来自子查询的实体,但我无法在文档或实验中找到如何选择多个实体.
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.
我的用例是我需要通过窗口函数过滤映射类底层的表,而在 PostgreSQL 中只能在子查询或 CTE 中完成.
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.
子查询跨越两个表的 JOIN,所以我不能只做 aliased(Entity1, subquery)
.
The subquery spans a JOIN of both tables so I can't just do aliased(Entity1, subquery)
.
推荐答案
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()
这篇关于SQLAlchemy ORM 从子查询中选择多个实体的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!