How to filter sqlalchemy query column in last child

Question

I currently have 2 tables roughly described as the 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 a one-to-many relationship from Parent to Child. I need to create a query to get all the parent elements, where their last (by timestamp) child element matches the given date range.

I can’t execute the subquery in the Child table, where I filter by date and use this in the join, because it will require to account for older elements as well.

Is there a way to create a query that only considers recent child times, or do I just need an extra column on the parent to track the child_id of the last child inserted?

Thanks for any help.

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