Select all forums and get latest post too.. how?

Question

I am trying to write a query to select all my forums and get the corresponding latest post (including the author)... but I failed.

This is my structure:

forums                      forum_threads              forum_posts
----------                  -------------             -----------
id                          id                        id
parent_forum (NULLABLE)     forum_id                  content
name                        user_id                   thread_id
description                 title                     user_id
icon                        views                     updated_at
                            created_at                created_at
                            updated_at
                            last_post_id (NULLABLE)

This is my current query:

SELECT forum.id, forum.name, forum.description, forum.icon, post_user.username
FROM forums AS "forum"
LEFT JOIN forum_posts AS "post" ON post.thread_id = (
    SELECT id
    FROM forum_threads
    WHERE forum_id = forum.id
    ORDER BY updated_at DESC LIMIT 1)
LEFT JOIN users AS "post_user" ON post_user.id = post.user_id
WHERE forum.parent_forum = 1
GROUP BY forum.id

Of course this query is incorrect, because there are many posts in one thread...

Can anyone help? I am using PostgreSQL btw.

Oh: I forgot: Currently I run through all "categories" (forums which have parent_forum = NULL) and then run an additional query for each forum (that's why you see parent_forum = 1 in my query). Is there a better way to do that?

EDIT: My last post is the post with newest date in updated_at in forum_posts

Answer 1

DISTINCT ON should make this easier:

SELECT DISTINCT ON (f.id)
       f.id, f.name, f.description, f.icon, u.username
FROM   forums             f
LEFT   JOIN forum_threads t ON t.forum_id = f.id
LEFT   JOIN forum_posts   p ON p.thread_id = t.id
LEFT   JOIN users         u ON u.id = p.user_id
WHERE  f.parent_forum = 1
ORDER  BY f_id, p.updated_at DESC;

According to your Q update the latest post is the one with the latest forum_posts.updated_at.
Assuming the column is defined NOT NULL.

Detailed explanation:
Select first row in each GROUP BY group?

Answer 2

Is this what you have in mind? You can get the latest post for a given forum using a subquery.

SELECT forums.id, forums.name, forums.description, forums.icon,
  (SELECT username FROM forum_threads AS ft
  JOIN forum_posts AS fp ON ft.id = fp.thread_id
  JOIN users AS u ON fp.user_id = u.user_id
  WHERE ft.forum_id = forums.id
  ORDER BY updated_at DESC LIMIT 1) AS username_of_latest_post
FROM forums