SQLAlchemy中同时过滤父对象与子对象的实现问题

模型结构 #

两个模型均使用start_date和end_date作为复合主键的一部分，记录行的有效日期范围：

class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)
    parent_number = Column(String)
    start_date = Column(DateTime, primary_key=True)
    end_date = Column(DateTime)
    children= relationship("Child", lazy="selectin")


class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    start_date = Column(DateTime, primary_key=True)
    end_date = Column(DateTime)
    parent_id = Column(Integer, ForeignKey("parent.id"))

需求 #

查询满足指定日期条件的Parent对象，同时其关联的children数组也必须满足相同的日期条件。

尝试过的方法及问题 #

1. 基础查询

session.query(Parent)
.filter(Parent.parent_number == '1234')
.filter(Parent.start_date > given_date & Parent.end_date < given_date)

生成两条SQL：第一条正确过滤了Parent的日期，但第二条加载Child的SQL未添加日期过滤，导致返回所有关联的Child。

2. 直接添加Child过滤条件

session.query(Parent)
.filter(Parent.parent_number == '1234')
.filter(Parent.start_date > given_date & Parent.end_date < given_date)
.filter(Child.start_date > given_date & Child.end_date < given_date)

仅修改了第一条查询的条件，第二条加载Child的SQL仍无过滤。

3. 关联查询

session.query(Parent)
.join(Child)
.filter(Parent.parent_number == '1234')
.filter(Parent.start_date > given_date & Parent.end_date < given_date)
.filter(Child.start_date > given_date & Child.end_date < given_date)

未达到预期效果，仍无法过滤关联的Child。

解决方案 #

1. 修正条件优先级问题 #

首先注意：&的优先级高于比较运算符，原条件会被错误解析，需改为用逗号分隔（SQLAlchemy中逗号等价于AND）或添加括号：

# 推荐用逗号分隔，更清晰
.filter(Parent.start_date > given_date, Parent.end_date < given_date)
# 或者加括号
.filter((Parent.start_date > given_date) & (Parent.end_date < given_date))

2. 对关联的Child添加加载过滤 #

因为children关系使用lazy="selectin"，需要使用selectinload并附加过滤条件，控制加载Child时的查询：

from sqlalchemy.orm import selectinload

session.query(Parent)
.filter(Parent.parent_number == '1234')
.filter(Parent.start_date > given_date, Parent.end_date < given_date)
.options(
    selectinload(Parent.children).filter(
        Child.start_date > given_date,
        Child.end_date < given_date
    )
)

此方式生成的第二条加载Child的SQL会自动带上日期过滤条件，符合预期。

3. 另一种方式：使用JOIN加载并过滤 #

如果希望用单条JOIN查询完成过滤，可以结合joinedload和contains_eager：

from sqlalchemy.orm import joinedload, contains_eager

session.query(Parent)
.join(Child)
.filter(Parent.parent_number == '1234')
.filter(Parent.start_date > given_date, Parent.end_date < given_date)
.filter(Child.start_date > given_date, Child.end_date < given_date)
.options(contains_eager(Parent.children))

这种方式会生成一条JOIN查询，同时过滤Parent和Child的条件，返回的Parent对象中仅包含符合条件的Child。

内容的提问来源于stack exchange，提问作者GuillaumeA