[SQL/Python] Writing SQL Statement for specific use-case

[Zalosath]

Hi,

 

I'm writing a forum style web page for a project and I'm having a little trouble writing an SQL statement for the below use-case:

I have a "Posts" table that stores all posts, including the original post and any replies, this structure is as follows (left some irrelevant information out):

post_id # the ID of this post
forum_id # the ID of the forum this post was made in
parent_post_id # the ID of the post it refers to, NULL if it's an original post
timestamp # when the post was posted

When I fetch the posts to display on a forum page, I want to order them by most recent activity, but I can't figure out how to do that.

 

I've got this:

cursor.execute(f"SELECT post_id, post_title, post_author, timestamp FROM posts WHERE forum_id='{forum_id}' AND parent_post_id IS NULL ORDER BY timestamp ASC")

Which only considers the original post, so basically I need a way to select only original posts but that are ordered by the most recent post in the posts table for that post id.

 

Any ideas?

 

[Zalosath]

A bit complex but I got it working:

SELECT p.*
FROM posts p
LEFT JOIN (
  SELECT parent_post_id, MAX(timestamp) AS max_timestamp
  FROM posts
  WHERE parent_post_id IS NOT NULL
  GROUP BY parent_post_id
) p2 ON p.post_id = p2.parent_post_id
WHERE p.parent_post_id IS NULL
AND forum_id=1
ORDER BY p2.max_timestamp DESC