Long ago, another content innovator told me about a scheme he had created that involved using floating point fields to retrieve an entire threaded comment tree using only a single sql query. When I questioned the wisdom of using floating point calculations, Yanthor said the floating point math more than made up for the time that would otherwise be used in executing a separate query for each nesting level, and challenged me to see if I could think of another way to do it.

I hereby present a code snippet or two from the lounge's thread code. A single simple query retrieves an entire thread tree, and perl code presents it in a nested format (some of the more mundane bits omitted for the sake of brevity):

# Retrieves the html of a thread sub get_content { # ... (some trivial code omitted) $self->{_comments} = {}; my $query = "SELECT comment.id, parent_id, article_id, user_id, subject, body, begin_date, submit_date, handle FROM comment, users WHERE user_id = users.id AND article_id = $self->{_article_id} ORDER BY comment.submit_date"; my $sth = $dbh->prepare($query); $sth->execute || die $DBI::errstr; while (($id, $parent_id, $article_id, $user_id, $subject, $body, $begin_date, $submit_date, $handle) = $sth->fetchrow()) { push @{$self->{_comments}->{$parent_id}}, { id => $id, parent_id => $parent_id, article_id => $article_id, user_id => $user_id, subject => $subject, body => $body, begin_date => $begin_date, submit_date => $submit_date, user_handle => $handle }; } # shows the thread tree starting at the # top and nesting down $content .= $self->show_tree($self->{_comments}, 0, 0); } sub show_tree { my ($self, $comments, $parent_id, $nest_level) = @_; my $content; # Show each comment foreach my $comment (@{$comments->{$parent_id}}) { # show the comment itself $content .= $self->comment_html( $comment, $nest_level, 1); # Recursion is fun $content .= $self->show_tree( $comments, $comment->{id}, $nest_level + 1); } return $content; }

I left out the actual comment_html function, as the only thing is does is return a string containing html populated by fields in the $comment hash reference.

Basic explanation: It runs a query which returns every comment associated with an article ordered chronoligically. It puts them into a data structure consisting of a hash whose keys are parent_id fields. The values are array references. Each array contains comments that are the direct children of the parent_id from the key of the hash.

In this way, the immediate children of any comment can be accessed through the array @{$self->{_comments}->{$parent_id}}.

Each member of that array is a reference to a simple old fashioned key => value hash, which contains the meat of the commment (body, subject, author, etc).

The following comment, which I put in the code to help ease my own conceptualization, might be of interest:

# Here's what the comments structure looks like # For the conceptually-impaired (like me) # $comments = { # # keyed on parent_id # # top-level comments, parent_id = 0 # 0 => [ # { # id => 2, # user_id => 7, # subject => "something" # body => "something" # parent_id => 0 # }, # { # id => 3, # user_id => 7, # subject => # body => # parent_id => 0 # }, # ], # # # keyed on parent id # 2 => [ # # array of replies to comment 2 # { # id => 4, # user_id => 5 # subject => # body => # parent_id => 2 # }, # { # id => 5, # user_id => 9 # subject => # body => # parent_id => 2 # }, # ], # }

I use the recursive function show_tree() to pump out the thread itself.

So there it is. A single query to retrieve a threaded structure with no floating point math.

Biggest limitation: It does have to retrieve all the comments associated with the given article every time, so it can't use a query for just a given sub-thread using this method. (It could certainly be displayed that way, but the query would still grab the whole mess.) For my purposes at the moment, that limitation is a non-issue.

I love perl. (The same thing could be done in php using very similar means though.)

So now that I've probably bored and/or confused everyone who is still reading this to death, I'll wander off now. I suddenly find myself wishing I had implemented a preview feature, since I'm not sure how all this code mess is going to turn out on the page. Oh well. Here goes...

Ok, I cheated with that one. It took me like half a dozen delete and re-submit cycles before all that crazy code looked normal. This thing definitely needs a preview mode.

Though I imagine most comments won't be nearly as complex or messy as that one. Consider it part of my stress testing round.

Either I was tired while I wrote that comment, or I'm tired now reading it. Could it get any worse?

"The keys of this simple hash reference are equal to a value in the sub-hash of the arrays pointed to by the values of the top-level hash, and..." blah blah blah.

I think it's bedtime now.

Yo Bitscape, I've noticed Movable Type has an interesting Traceback system that shows the readers referers for a particular article. I find it kind of interesting to see who's linking to what.

My theory, based on occassional rudimentary logfile analysis, suggests that this site is mostly just visited by people I know or have known in real life. It might be fun to post the occassional referrer that dumps somebody in from a search engine, or even the rare links by real humans (other than people I know) to this obscure site.