CREATE DEFINER=`stuffbyd`@`localhost` PROCEDURE `forum_list`(IN `forum_id` INT)
    NO SQL
BEGIN
        SET max_sp_recursion_depth = 255;

        CALL forum_list_children(forum_id, @latest_post, @latest_post_date, @topics, @posts);

        SELECT  forum.name AS name,
            forum.description AS description,
        @topics AS topics,
        @posts AS posts,
            post.id AS latest_post,
                post.DATE AS latest_post_date,
            topic.id AS latest_post_topic,
                topic.title AS latest_post_topic_title,
                member.id AS member,
                member.username AS username
        FROM post
        LEFT JOIN topic ON post.topic = topic.id
        LEFT JOIN member ON post.member = member.id
        LEFT JOIN forum ON forum.id = forum_id
        WHERE post.id = @latest_post;
END

CREATE DEFINER=`stuffbyd`@`localhost` PROCEDURE `forum_list_children`(IN `forum_id` INT, OUT `forum_latest_post` INT, OUT `forum_latest_post_date` DATETIME, OUT `forum_topics` INT, OUT `forum_posts` INT)
    NO SQL
BEGIN
        DECLARE cur_id, cur_forum_latest_post, cur_forum_topics, cur_forum_posts INT;
        DECLARE cur_forum_latest_post_date datetime;
        DECLARE done BOOLEAN DEFAULT FALSE;
        DECLARE cur cursor FOR SELECT id FROM forum WHERE parent = forum_id;
        DECLARE continue handler FOR NOT found SET done = TRUE;

        -- Find latest post and its date from topics
        SELECT id, DATE INTO forum_latest_post, forum_latest_post_date FROM (
            SELECT post.id AS id, post.DATE AS DATE, topic.forum AS forum
            FROM post
            LEFT JOIN topic
            ON post.topic = topic.id
        ) x WHERE forum = forum_id
        ORDER BY DATE DESC
        LIMIT 1;

        -- Find amount of topics and posts
        SELECT COUNT(id) INTO forum_topics FROM topic WHERE topic.forum = forum_id;
        SELECT COUNT(id) INTO forum_posts FROM (
            SELECT post.id AS id, topic.forum AS forum
            FROM post
            LEFT JOIN topic
            ON post.topic = topic.id
        ) x WHERE forum = forum_id;

        OPEN cur;
        my_loop: loop
            fetch cur INTO cur_id;
                IF done THEN
                    leave my_loop;
                END IF;

                -- Call same function for child
                CALL forum_list_children(cur_id, @cur_forum_latest_post, @cur_forum_latest_post_date, @cur_forum_topics, @cur_forum_posts);

                -- Is the newest post from this child newer than the previously found?
                IF (@cur_forum_latest_post_date > forum_latest_post_date) THEN
                    SET forum_latest_post = @cur_forum_latest_post;
                        SET forum_latest_post_date = @cur_forum_latest_post_date;
                END IF;

                -- Add topics and posts from child to total
                SET forum_topics = forum_topics + @cur_forum_topics;
                SET forum_posts = forum_posts + @cur_forum_posts;
        END loop my_loop;
        close cur;
END