Arrange forum posts by time of latest reply

Before you shout ORDER BY id, the situation is quite different.

The forum I was asked to make is a guestbook forum, where unregistered users can reply to and make posts. Replies to a post are indented under the post being replied to. Of course, this is all in unordered lists. A short example:

• Main post
• • Reply to post
• • • A reply to the reply
• • Another reply to main
• A post that has nothing to do with the replies

It's like what Nettuts+'s comment system looks like. In the database, a post has all the obvious things (id, message body, author, time...) and a replyid. The replyid basically means what is this post a reply to. If replyid is 0, it's a main post (thread in ordinary forum means.)

And that's how I go around displaying these posts: At first, I call a function (let's call it showPosts) which has an optional param called replyid; defaults to 0.

In showPosts I grab all posts with a replyid equal to the param from the database in an associative array, and populate the posts field in the array with the results of showPosts, and I pass to showPosts the id of the post. In the end of showPosts I return that associative array. If this was unclear, here's the snippet:

 function showPosts($postid = 0) {
        $query = query("SELECT * FROM posts WHERE replyid='$postid'");
        $r = array();
        $i = 0;
        while (@$row = $query->fetch_assoc()) {
            $r[$i] = $row;
            $r[$i]['posts'] = showPosts($row['id']);
            ++$i;
        }
        return $r;
    }

Everything's working great, like expected, but I am stomped by this problem: When a user replies to an old post, I want that post to show first, just like you have in forum threads.

I've already thought about making a field in the database called lastChanged, and whenever a user posts a new reply, it goes all the way up the posts chain and changes each of their lastChanged value to the time of posting. However, this seemed just a waste of memory and a possible time killer. I've also thought of seperating main threads from replies, but that'd make things a little more complicated on the mind, and I like the beauty of not making a difference between a thread and a post, since anything can be either a thread or a post.

Just so you know, the database is MySQL and backend written in php (and of course, interacting with MySQL using MySQLi.)

Thanks in advance, and sorry if anything is unclear.

Edit: The db's structure, as requested.

id      |  int(11)
title   |  varchar(100)
author  |  varchar(100)
body    |  text
replyid |  int(11)
time    |  datetime

Given the comments in the original question between myself and Zirak, I'd propose the following answer.

He's right — traversing the whole tree to find the date of the most recent comment may take a while, as it would seem there's no limit to the number of levels of nesting that can take place in the system. I'm aware that this is a lot of repetition of data, but why not store something like main_post_id, which refers to the root node (main post). That way you can ORDER BY max(time) WHERE main_post_id = 1.

If it's forum software (which I assume therefore may also involve things like pagination of lists of threads), then this would make life a lot easier, as you could perform the sorting at the database query level, rather than in the PHP.

If you don't want to go with lastChanged

You have to compute last time of the last comment in the display section (in the PHP method you posted), and when you have for the parent posts the last modified date, you can do a sort on the php array.

This walk won't be anything in plus, since you already have recursion, you just have to figure out the proper way to up-walk the date for the last reply and sort the array before final return.

Something like this (without sort)

function getPost($postid = 0) {
        $query = query("SELECT * FROM posts WHERE replyid='$postid'");
        $r = array();
        $i = 0;
        $recenttime=0;
        while (@$row = $query->fetch_array(MYSQLI_ASSOC)) {
            $r[$i] = $row;
            if (strtotime($row['time'])>$recenttime) {
                $recenttime=strtotime($row['time']);
            }
            $r[$i]['posts'] = $this->getPost($row['id']);
            if ($r[$i]['posts']['recenttime']>$recenttime) {
                $recenttime=$r[$i]['posts']['recenttime'];
            }
            ++$i;
        }
        $r['recenttime']=$recenttime;//save recenttime for this level
        if ($postid == 0) {
            // do the sort here based on $r[$i][$recenttime];
        }
        return $r;
}

Hmm.. considering that reading the list is executed far more often than writing. And also considering that reading involves far more data I would prefer to put the load on write time.

With the lastChanged solution on root level you can also do the list with reading far less rows with a simple SQL Query instead of reading x numbers of rows.

Sams solution has the disadvantage, that you need a additional GROUP BY, which leads to a temporary table, which slows down your sql server.