How to filter a sqlalchemy query by a column in latest child item

Question

I currently have 2 tables roughly described as following SQLAlchemy mapping:

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))

So I have one-to-many relationship from Parent to Child. What I need to do is create a query to get all Parent items where their latest (by timestamp) Child item matches a given date range.

I can't do a subquery on Child table where I filter by date and use that in a join because that would take in to account older items too.

Is there a way to construct query that only takes account the latest child times or do I simply need an extra column on Parent to track the child_id of the latest inserted Child?

Thanks for any help.

Answer 1

First, use a subquery to get the latest child timestamp for each parent. Then query for parents, joining on the subquery to use for filtering.

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()