Threaded Forums Made Easy

Not too much about Perl, but just wanted to say that sometimes I have a lovely feeling of accomplishment after struggling to find a good approach. After considering my options about forums, I decided to continue on my own. With the proper tool selection, they're incredibly easy. Below is sample output of my alpha code.

Threaded Forum

I can pull all replies to a forum post, properly sorted, with a single SQL statement. Each post is simply formatted with this:

[% WHILE ( post = ) -%]
   [% level = post.levels; IF level > 6; level = 6; END -%]
  <li class="comment level[% level %]">
    <div class="comment-date">[% post.created %]</div>
    <div class="comment-title"><a href="[% c.uri_for('/post', %]">[% post.title | html %]</a></div>
    <div class="comment-author">by [% | html %]</div>
    <div class="comment-reply">[<a href="[% c.uri_for('/post',, 'reply') %]">reply</a>]</div>
    <div class="comment-body">[% post.body | html %]</div>
[% END %]

The output is ugly and the Template Toolkit code does need need some serious cleaning up (URLs are bad and I shouldn't hard-code the post depth), but it's so much easier than I thought.

After I have a few more things sorted out, I'll post some code snippets on how I made this work (mostly through a clever stored procedure in PostgreSQL).


You can also use arrays in PostgreSQL for your materialized paths, PostgreSQL sorts arrays sensibly (i.e. term by term) and they're a natural representation for the paths. You just need to keep track of the branch numbers and let a trigger maintain the branch numbers and path arrays.

You can also use a single string for the paths by representing each branch number with a fixed number of base-64 digits and sorting them ASCII-betically, two or three digits per branch should suffice. This yields a smaller and somewhat more manageable sort key than the matpath or sortpath examples from Stack Overflow.

Why do you even need to encode it with base64? Postgres has bytea column type where you can put raw bytes, so if your row ID is an INT, you only need 4 bytes per element. Yay!

About Ovid

user-pic Freelance Perl/Testing/Agile consultant and trainer. See for our services. If you have a problem with Perl, we will solve it for you. And don't forget to buy my book!