Here's my situation. I have to tables

Parent

Children

What I would like to do, using SQLAlchemy in Python, is to retrieve the most recent Children for every parent. The result of the query would return Children with IDs 2 and 4 since they are the most recent.

CodePudding user response：

As for constructing the query in SQL, the cleanest way to achieve this is to use Postgre's DISTINCT ON feature:

SELECT DISTINCT ON (parent_id) *
FROM Children
ORDER BY parent_id, time_created DESC;

Based on this answer, this could be mapped to the following SQLAlchemy code:

latest_children = Children.query.\
    distinct(Children.parent_id).\
    filter_by(**filter_by_query).\
    filter(*queries).\
    order_by(Children.query, Children.time_created.desc()).\
    all()