Message Board Quandary, Revisited

stonecolddevin has asked for the wisdom of the Perl Monks concerning the following question:

Howdy all

I've got this bit of code I stole from a somewhat dated Perl CGI book I checked out from the local school library.

What it does is basically retrieve a parent thread from a message board and all the children threads in association with it, thereby fulfilling my quandary. I updated this code to play ok with Catalyst/T::T/DBIx::Class.

Great, grand, dandy.

What I'd like to do now (besides put it all in a ::ResultSet class for DBIx::Class) is:

Change the indentation via "&nbsp" and convert it to a nested list (much like our very own here at Perlmonks)
Possibly optimize it some?
Make it more DBIx::Class friendly, i.e. do this through a relationship and build a tree some how.

Thankfully, most of the work is done, it just seems to need refinement. I've checked out DBIx::Class::Tree and don't really grok it. Aside from binary search trees, I'm a bit useless when it comes to trees as I've not had much experience otherwise.

The code:

package OnTheBeachDB::Messages;
    
use base qw/DBIx::Class/;  
    
# Load required DBIC stuff
__PACKAGE__->load_components(qw/PK::Auto Core/);
# Set the table name
__PACKAGE__->table('messages');
# Set columns in table
__PACKAGE__->add_columns(qw/message_id parent_id created title name li
+nk message/);
# Set the primary key for the table
__PACKAGE__->set_primary_key(qw/message_id/);

###########################################
##
##   THIS WHOLE THING IS TO BECOME A ResultSet CLASS
##
###########################################

sub create {
    
    ## MAKE ME PLUGINS #####
    require HTML::Scrubber; 
    require DateTime; #POSIX;
    ########################

    my ( $self, 
     $c,
     $name,
     $title,
     $message,
     $parent_id ) = @_;
    
    
    ## hackish right now, needs to go in Forum::Util 
    ## or some such
    my @lt = localtime(time);
    my $date = DateTime->now; #POSIX::strftime('%Y-%m-%d %H:%M:%S', @l
+t);
     
    ## create the HTML::Scrubber object to un-taint 
    ## form data and dis/allow HTML
    my $scrubber = HTML::Scrubber->new( allow => [ qw[ p b i u hr br ]
+ ] ); 
                  
    # cleaninate!                                                     
+                       
    my $clean_name    =  $scrubber->scrub($name);
    my $clean_title   =  $scrubber->scrub($title);
    my $clean_message =  $scrubber->scrub($message);   
    
    # insert into db
    $c->model('OnTheBeachDB::Messages')->create({
    
         name      => $clean_name,
         title     => $clean_title,
         message   => $clean_message,
         created   => $date,
         parent_id => $parent_id,
    
    });
         
     
    
}

sub get_thread_ids {
    
    my  ( $self, $c ) = @_;
    my @ids;
    my $rs = $c->model('OnTheBeachDB::Messages')->search({ parent_id =
+> '0' });
    
    while ( my $id = $rs->next ) {
        
        push ( @ids,  $id->message_id );
        
    }
    
    return @ids;
    
}   

sub get_thread_messages {
    
    my ($self,  $c, $parent_id, $depth, $replies ) = @_;
    my $output;
    
    ## if no replies, set up the link given
    if ( !$replies) {
        
        $output .= $self->generate_link( $c, $parent_id, $depth );
        
    }
    
    ## Get the ids of any children
    my $rs = $c->model('OnTheBeachDB::Messages')->search( 
        {
            parent_id => $parent_id    
        },
        {
            group_by  => 'created',
        }
    );
    
    ## Loop on each child found, if it has children, 
    ## recurse on this function, if now just generate it's link
    while ( my $id = $rs->next ) {
        
        ## Let's see if this child message has children of it's own
        if ( $self->has_children( $c, $id->message_id ) ) {
            
            $output .= $self->get_thread_messages( $c, $id->message_id
+, $depth + 2 );
            
        } else {
            
            $output .= $self->generate_link( $c, $id->message_id, $dep
+th + 2 );
            
            
        }
        
    }
    
    return $output;
    
} 

## See if this ID has any children
sub has_children {
    
    my ( $self, $c, $id ) = @_;
    
    my $count = $c->model('OnTheBeachDB::Messages')->search(
        {
             message_id => $id,
        }
    )->count();
    
    if ( $count > 0 ) {
        
        return 1;
        
    } else {
        
        return 0;
        
    }
    
}

## Count how many children this thread has
sub number_of_children {
    
    my ( $self, $c, $id ) = @_;
    
    my $count = $c->model('OnTheBeachDB::Messages')->search(
        {
             parent_id => $id,
        }
    )->count();
    
    return $count;

}
    

## This is what you will see on the actual page
## Generate a link, with username, thread title, date
## Add &nbsp;&nbsp; per $depth size
sub generate_link {
    
    my ( $self, $c, $id, $depth ) = @_;
    my $output;
    
    my $rs = $c->model('OnTheBeachDB::Messages')->find($id);
    
    my $title        = $rs->title;
    my $name         = $rs->name;
    my $created      = $rs->created;
    my $thread_text  = $rs->message;
    
    ##### DEFINITELY want to get this into a template somehow. Todo li
+st. ##
    ## Indendation multiplied by the supplied $depth number
    my $spaces = '&nbsp;&nbsp;' x $depth;
    
    
    $output .= $spaces . 
               qq!<a href="! . $c->uri_for('view/') . $id . qq!">! .
               qq!$title</a>! . 
               qq!&nbsp;by <a href="! . $c->uri_for('/users/') . $name
+ . qq!">$name</a>! .
               qq!&nbsp;on $created <br />\n! . 
               $spaces . qq!$thread_text<br />\n! . 
               $spaces . qq!<a href="! . $c->uri_for('reply/') . $id .
+ qq!">comment on ! . $title . qq! </a><br /><br />!;
    ##################
}
=head1 NAME
    
    OnTheBeachDB::Message - A model object representing a forum messag
+e.
    
=head1 DESCRIPTION
  
=cut
    
1;
[download]

Calling them:

sub index : Private {
    
    my ( $self, $c ) = @_;
    my @threads;
    
    ## set up the query to get all threads that aren't children
    my $rs = $c->model('OnTheBeachDB::Messages')->search ( { parent_id
+ => '0' }, { order_by => 'created DESC'} );
    
    
    ## loop through the parent threads, 
    ## count the number of children,
    ## and push a reference to %thread_info into @threads
    
    while ( my $thread = $rs->next ) {
        
        my %thread_info;
             
        $thread_info{'name'}          = $thread->name;
        $thread_info{'created'}       = $thread->created;
        $thread_info{'title'}         = $thread->title;
        $thread_info{'message_id'}    = $thread->message_id;
        $thread_info{'message'}       = $thread->message;
        $thread_info{'comment_count'} = OnTheBeachDB::Messages->number
+_of_children( $c, $thread->message_id );
        push (@threads, \%thread_info)
   
        
    }
    
    $c->log->debug($c->model("OnTheBeachDB::Forums")->find(1)->posts_r
+s->next->title);
   
    $c->stash->{page}     = $c->model('OnTheBeachDB::Page')->find({ na
+me => "forum_main" });
    $c->stash->{threads}  = \@threads;
}

sub create : Local {
    my ( $self, $c ) = @_;
    
    
}

sub do_create : Local {
    my ( $self, $c ) = @_;
    
    ## for validation
    #unless ( $c->form->has_error ) {
        
       OnTheBeachDB::Messages->create(
           $c,
           $c->req->param('name'),
           $c->req->param('title'),
           $c->req->param('comments'),
           0, # since it's a new thread
       
       ); 
            
    #} else {
        
    #    $c->detach('create');
        
    #}
    
}

sub reply : Local {
    my ( $self, $c, $replyid ) = @_;
    
    my $thread = $c->model('OnTheBeachDB::Messages')->find($replyid);
    $c->stash->{thread} = $thread;
    
    
}

sub do_reply : Local {
    
    my ( $self, $c ) = @_;
    
    ## form validation
    ## change to ::Controller::FormBuilder
    #unless ( $c->form->has_error ) {
        
       OnTheBeachDB::Messages->create(
           $c,
           $c->req->param('name'),
           $c->req->param('title'),
           $c->req->param('comments'),
           $c->req->param('parent_id'), # since it's a new thread
       
       ); 
            
    #} else {
        
    #    $c->detach('create');
        
    #}
    
    $c->stash->{template } = 'forum/do_create.tt2';
    
}

sub view : Local {
    my ( $self, $c, $id ) = @_;
    
    my $thread = OnTheBeachDB::Messages->get_thread_messages($c, $id, 
+'replies');
    
    
    $c->stash->{threads}  = $thread;
}
[download]

I've also put some thought into clinton's advice about using a join table located here. This would be a bit more along the lines of something I'm considering, however, I still need to build a tree :-).

I may be getting ahead of myself here but I'm sort of wary on join tables. The more queries the more prone to speed loss an application is it seems, so unless I'm also wrong here, I could use some help in the way of optimizing it to as few queries as possible without losing the ability to look at my code and have it Make Sense.

Thank you kind monks!

UPDATE: Added code showing how these methods are called.

meh.

Comment on Message Board Quandary, Revisited Select or Download Code

Replies are listed 'Best First'.
Re: Message Board Quandary, Revisited by Corion (Patriarch) on Jun 02, 2008 at 08:59 UTC
I voted on nodes in your previous post regarding this, so I must have seen it, but it seems I didn't mention Celko's methods of storing such trees using SQL. I heartily recommend Celko's SQL for Smarties, which treats the matter. A smaller book on this specific topic is Trees and Hierarchies in SQL for Smarties, but that is mostly a rehash of his SQL column which are available on the web. The idea that seems the one most convenient and transparent to me is to store the location of a node in the tree as a string which traces the path to the tree: create table nodes ( node_id integer not null primary key, parent integer references node_id, message varchar(1024), location varchar(256) -- only 256 levels of replies and only 256 replies to a root node ); -- Let's create a discussion -- root level insert into nodes (node_id, message,location) values (1,'Hello',''); -- we count from 'A' to 'Z' in this simplicistic example insert into nodes (node_id, message,location) values (2,'Re: Hello','A +'); insert into nodes (node_id, message,location) values (3,'Re: Hello','B +'); insert into nodes (node_id, message,location) values (4,'Re: Re: Hello + (1)','AA'); insert into nodes (node_id, message,location) values (5,'Re: Re: Hello + (2)','AB'); insert into nodes (node_id, message,location) values (6,'Re: Re: Hello +!','BA'); [download] If you want all children of the node number 2, you can run a LIKE query against your table and you will get back the list of nodes in one query: `select node_id, location from nodes where location like 'A%' order by location asc` [download] This saves you the issuing of multiple queries against your database at the price of having a set limit on the number of replies to a node and at the cost of an (anchored) LIKE query. A LIKE query might or might not use an index, depending on your database, so you have to check that. I've demonstrated a very simplicistic approach of using alphabetic characters to denote the position in the tree. You can expand that approach by using multiple characters per depth (for example `sprintf '%04d'` if you want to keep ASCII), but the fixed limit will remain. I found some articles by Celko on the subject and likely this Google query will turn up more...	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Message Board Quandary, Revisited
by Corion (Patriarch) on Jun 02, 2008 at 08:59 UTC

I voted on nodes in your previous post regarding this, so I must have seen it, but it seems I didn't mention Celko's methods of storing such trees using SQL. I heartily recommend Celko's SQL for Smarties, which treats the matter. A smaller book on this specific topic is Trees and Hierarchies in SQL for Smarties, but that is mostly a rehash of his SQL column which are available on the web.

The idea that seems the one most convenient and transparent to me is to store the location of a node in the tree as a string which traces the path to the tree:

create table nodes (
    node_id integer not null primary key,
    parent  integer references node_id,
    message varchar(1024),
    location varchar(256)
    -- only 256 levels of replies and only 256 replies to a root node
);

-- Let's create a discussion
-- root level
insert into nodes (node_id, message,location) values (1,'Hello','');

-- we count from 'A' to 'Z' in this simplicistic example
insert into nodes (node_id, message,location) values (2,'Re: Hello','A
+');
insert into nodes (node_id, message,location) values (3,'Re: Hello','B
+');
insert into nodes (node_id, message,location) values (4,'Re: Re: Hello
+ (1)','AA');
insert into nodes (node_id, message,location) values (5,'Re: Re: Hello
+ (2)','AB');
insert into nodes (node_id, message,location) values (6,'Re: Re: Hello
+!','BA');
[download]

If you want all children of the node number 2, you can run a LIKE query against your table and you will get back the list of nodes in one query:

select node_id, location from nodes
where location like 'A%'
order by location asc
[download]

This saves you the issuing of multiple queries against your database at the price of having a set limit on the number of replies to a node and at the cost of an (anchored) LIKE query. A LIKE query might or might not use an index, depending on your database, so you have to check that. I've demonstrated a very simplicistic approach of using alphabetic characters to denote the position in the tree. You can expand that approach by using multiple characters per depth (for example sprintf '%04d' if you want to keep ASCII), but the fixed limit will remain.

I found some articles by Celko on the subject and likely this Google query will turn up more...

[reply]
[d/l]
[select]