I have an SQLAlchemy model called Post. A post can have a parent post (foreign key on post.id, adjacency list pattern), and children (posts where its id is the parent_id). Each post also contains its materialized path (post.ancestry) to make querying descendants easy.
>>> post.creation_date
<<< 2013-07-24 20:39:56.158990
>>> post.parent_id
<<< 14
>>> post.ancestry
<<< '1,3,5,11,14,'
My challenge is to get a list of root posts (posts without parents) and sort them by the age of their youngest descendant, the way GMail does with the inbox. The conversation at the top has the newest message, and the conversation at the bottom of the inbox has the oldest message.
What would the query for this look like?