Here's my situation. I have to tables
Parent
| id | other |
|---|---|
| 1 | ... |
| 2 | ... |
| 3 | ... |
| 4 | ... |
Children
| id | parent_id | time_created |
|---|---|---|
| 1 | 1 | 2022-11-17 13:18:49 |
| 2 | 1 | 2022-11-17 13:47:05 |
| 3 | 2 | 2022-11-18 12:00:22 |
| 4 | 2 | 2022-11-18 16:06:17 |
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.
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()
