PHP/MySQL: A single SQL query to fetch forum name, sum total of topics, posts and the LAST POST (post id, topic id and username)

Question

Ok, so this is not a new problem to me but since sophisticated SQL queries is not among my best talents I would typically break it apart. Still, I'm pretty sure a single query is possible and this time around I feel like doing it right but I simply can't put my finger on it. The following is as far as I could go:

    SELECT forums.forum, forums.description, topics.forum_id, 
    (SELECT COUNT(id) FROM topics WHERE forum_id = forums.id) AS num_topics, 
    (SELECT COUNT(id) FROM posts WHERE topic_id = topics.id) AS num_posts,
    MAX(posts.id) AS last_post
    FROM forums
    LEFT JOIN topics ON topics.forum_id = forums.id
    LEFT JOIN posts ON posts.topic_id = topics.id
    LEFT JOIN users ON users.id = posts.author_id

So I select forum id, forum name, short description and forum id aqcuired from topics table because the column name is unique by default. Then I use subqueries to count the sum total of topics and posts per forum (is there any other way to do it?). And then comes the most tricky part. I can get the last post per forum (MAX(post.id)) but try as I might I can't get the topic id and name for it, as well as the post author, which I never even got close to. Doing something like (SELECT topic_id FROM posts WHERE id = last_post) is not working, of course, and I don't understand why.

Answer 1

This will not be quick

SELECT forums.
    forum, forums.description, topics.forum_id, 
    (SELECT COUNT(id) FROM topics WHERE forum_id = forums.id) AS num_topics, 
    (SELECT COUNT(id) FROM posts WHERE topic_id = topics.id) AS num_posts,
    MAX(posts.id) AS last_post,
    (SELECT topic_id FROM posts ORDER BY id DESC LIMIT 1) LAst_topic_id,
    (SELECT topic_name FROM topics WHERE id = (SELECT topic_id FROM posts ORDER BY id DESC LIMIT 1)) LAst_topic_name
FROM forums
LEFT JOIN topics ON topics.forum_id = forums.id
LEFT JOIN posts ON posts.topic_id = topics.id
LEFT JOIN users ON users.id = posts.author_id

Or if the last id is not really the last id from the post tables

SELECT
    forum,description,forum_id,num_topics,num_posts,last_post,
    (SELECT topic_id FROM posts WHERE id = last_post) LAst_topic_id,
    (SELECT topic_name FROM topics WHERE id = (SELECT topic_id FROM posts WHERE id =  last_post)) LAst_topic_name
    FROM
        (SELECT 
            forums.forum, forums.description, topics.forum_id, 
            (SELECT COUNT(id) FROM topics WHERE forum_id = forums.id) AS num_topics, 
            (SELECT COUNT(id) FROM posts WHERE topic_id = topics.id) AS num_posts,
            MAX(posts.id) AS last_post
        FROM forums
        LEFT JOIN topics ON topics.forum_id = forums.id
        LEFT JOIN posts ON posts.topic_id = topics.id
        LEFT JOIN users ON users.id = posts.author_id) t1;