Ovid

I previously wrote about threaded forums and threatened to share some code with you. Basically, I want to get this in a single SQL call:

As it turns out, a lot of people want to get that in a single SQL call and some of the answers seem fairly complicated. I can't say my answer is necessarily the best one (and there are some caveats to it), but compare my stored procedure to the one in the previous link.

First, I assume that each post has a parent post and the root post should point to itself as the parent.

Then, each post should have a materialized path (and a tip of the pen to Matt Trout for describing them to me). A materialized path is simply the path to the current node, formed by concatenating parent ids with a separator. For example, if post 3's parent is post 2 and its parent is post 1 and that's the root path, post 3's materialized path might be 1.2.3. Then, to fetch a post (post 1 in this example) and all children, the SQL is simply:

SELECT * FROM posts WHERE id = 1 OR matpath LIKE '1.%';

However, this doesn't allow us to properly order our posts. When people post replies higher up a tree, it's very easy to get the posts returned in the wrong order. To handle this, I've added a 'sortpath' field. Since I automatically have a 'created' column, I can use dates and ids (the dates get me my sorting, but I append an ID to guarantee a deterministic sort order in case two posts come in at the same time). I then append the current created date (resolved to seconds) and the id to the parent sort path. I've chosen to do this with a PostgreSQL stored procedure. It looks like this:

CREATE OR REPLACE FUNCTION post_sortpath() RETURNS TRIGGER AS $BODY$
    DECLARE
        new_sortpath text;
        parent_post  forum_post%ROWTYPE;
    BEGIN
        new_sortpath = date_trunc('second', NEW.created) || '-' || NEW.id;
        IF NEW.parent_id IS NULL THEN
            NEW.parent_id := NEW.id;
            NEW.matpath   := NEW.id;
            NEW.sortpath  := new_sortpath;
        ELSE
            SELECT * INTO parent_post FROM forum_post WHERE id = NEW.parent_id;
            NEW.matpath  := parent_post.matpath  || '.' || NEW.id;
            NEW.sortpath := parent_post.sortpath || '.' || new_sortpath;
        END IF;
        RETURN NEW;
    END;
$BODY$ LANGUAGE 'plpgsql';
CREATE TRIGGER post_sortpath BEFORE INSERT ON forum_post 
    FOR EACH ROW EXECUTE PROCEDURE post_sortpath();

My DBIx::Class code to get replies for a given post now looks like this:

sub replies {
    my $self = shift;
    return $self->result_source->schema->resultset('ForumPost')->search(
        {
            topic_id => $self->topic_id,
            matpath  => { like => $self->matpath.".%" },
        },
        { order_by => 'sortpath' }
    );
}

(Note that the topic id is not strictly necessary, but if there's an index on that, it should filter forum posts much faster than the LIKE will).

There are a couple of caveats with this. Deeply nested replies will create a huge sortpath, so switching to a shorter implementation might be good. Also, because I populate this via a stored procedure, it means that every time I create a post, if I need this immediately I need to call $post->discard_changes to reload this from the database. However, it's not needed in my app, so that's not an issue. Your mileage may vary.

Another benefit of moving this from the database into your code (using DBIx::Class, you'd override the insert() method) is that you move load from the database to the application. Generally I would discourage this because it would be possible for rogue code to insert bad data. However, profiling database applications is always tricky and if you need to shift load from the server to the app, this might be one of those areas (for low-use forums, this is probably a very bad optimization).

Update: Peter Rabbitson pointed out to me that github has a DBIx::Class example of this being done in a single column. It's at DBIx::Class::Tree::Recursive, but the actual package is DBIx::Class::Tree::Ordered::MatPath. I think my implementation could also use a single column, but you'd want to get the sortpath column to have much shorter sort values rather than $date-$id for each segment.