如何通过最新子项的列过滤SQLAlchemy查询

Question

我现在有两个表，简单来说，它们的结构大概是这样的：

class Parent(Base):
    __tablename__ = "parent"

    parent_id = Column(Integer, primary_key=True)

class Child(Base):
    __tablename__ = "child"

    child_id = Column(Integer, primary_key=True)
    parent_id = Column(Interger, ForeignKey(parent.parent_id))
    child_timestamp = Column(TIMESTAMP)

    parent = relationship("Parent", backref=backref("children", order_by=child_id))

在这两个表中，Parent（父表）和Child（子表）之间是一对多的关系。我的需求是创建一个查询，找出所有Parent项，其中它们最新的Child项（根据时间戳）符合给定的日期范围。

我不能在Child表上做一个子查询来根据日期进行过滤，然后再用这个结果进行连接，因为那样会把旧的项也考虑进去。

有没有办法构建一个查询，只考虑最新的Child时间？还是说我需要在Parent表中增加一个额外的列，用来记录最新插入的Child的ID呢？

谢谢大家的帮助。

Answer 1

首先，使用一个子查询来获取每个父项最新的子项时间戳。然后查询父项，并通过这个子查询来进行筛选。

sub = session.query(
    Child.parent_id,
    func.max(Child.child_timestamp).label('child_latest')
).group_by(Child.parent_id
).subquery()

parents = session.query(Parent
).join((sub, sub.c.parent_id == Parent.id)
).filter(sub.c.child_latest.between(lower_date, upper_date)
).all()