in reply to Message Board Threading with DBI/MySQL

The other thing you can do is you can store the thread id as a string of the id's of the message's replied to representing the actual thread hierarchy. This allows you to implement the same kind of thing that you see on this site where it's clear when looking at the threads which level their at.

So, for example, message 1 has two replies (messages 2 & 3), and there are also two repies to message 2 (messages 4 & 5). The thread ids for the messages are:

Message: 1 Thread ID: NULL
Message: 2 Thread ID: "1"
Message: 3 Thread ID: "1"
Message: 4 Thread ID: "1_2"
Message: 5 Thread ID: "1_2"

This lets you use SQL LIKE to pick out the exact level you want, ie:

SELECT ... WHERE thread_id LIKE "1_2%";

to get all the messages that thread with a message at a given reply level, etc..

You can also you regular expression searches for more complex searches. Of course such searches won't be indexable.

Also, using this method, if you sort by the thread id when you get back a list of messages then you can easily examine the thread id string with Perl regular expresions to figure out at which level of indentation to display it!

Good luck!

Replies are listed 'Best First'.
Re: Re: Message Board Threading with DBI/MySQL
by Beatnik (Parson) on Jun 07, 2001 at 10:21 UTC
    What about inserting, deleting, moving, etc? That would ruin the 'hierarchial' structure if you're not gonna rewrite all child threads :)
    I usually stick to less obvious message ID format, like a time & date format (as unique key & incremented if necessary). To solve the pointer to the parent, you can either have a parent 'pointer' in your message ID, or have an actual parent field.

    Greetz
    Beatnik
    ... Quidquid perl dictum sit, altum viditur.
[reply]
      If you have to have that kind of flexibility then of course this system doesn't work. But usually, for a message board app, you don't need that. It's a "write once and leave it" kind of application. And let's say you do delete a message, the fact that it's gone doesn't destroy the relationships of all the other messages...
[reply]
        Even if it has 'child' messages, in case you have some kind of reply system? :) When deleting a message, you should also make sure to delete child messages, since orphan messages will get inaccessible if you dont (which ofcourse take up space, slow down queries, etc)

        Greetz
        Beatnik
        ... Quidquid perl dictum sit, altum viditur.
[reply]

In Section Seekers of Perl Wisdom