update - something like that to gather data:

sub get_blogs {
    my ($self, $c, $page) = @_;
    my $blogs; 
    foreach $blog ( 
        $c->model('YourSpaceDB::Blog')->search(
            { 
                blog_is_hidden => 0,
                blog_is_reply  => 0,
                blog_is_draft  => 0,
            },  
            {   
                rows           => 5,
                page           => $page,
                order_by       => 'blog_date DESC'
            }   
        )->all) {
        push @$blogs, get_replies($c, $blog, $page);
    }
    $blogs;
}
sub get_replies {
    my ($c, $blog, $page) = @_;

    my $replies;
    my $ret;
    
    my $replies = $c->model('YourSpaceDB::Blog')->search(
        {
            blog_is_hidden   => 0,
            blog_is_reply    => 1,
            blog_is_draft    => 0,
            blog_is_reply_to => $blog->blog_id,
        },
        {
            rows             => 5,
            page             => $page,
            order_by         => 'blog_date DESC',
        }
    );

    return [ $blog, 0, undef ] unless $replies;

    foreach my $reply (@$replies) {
        push @$ret, get_replies($c, $reply, $page);
    }
    [ $blog, scalar (@$replies), $ret ];
}
[download]

As I don't know anything about your data model, this is just a guess. The data structure would look like

$blogs = [ # blog list
  [ # first blog
    blog,
    2,
    [ # replies structure
      [ # first reply
        reply,
        0,
        undef, # no replies
      ], 
      [ # second reply
        reply,
        1,
        [ # replies structure
          [ # first reply 
            reply,
            0,
            undef, # no replies
          ]
        ]
      ], 
    ],
  ], # end of first blog
  [  # second blog
    ...
  ],
]; # end of blog list
[download]

Roughly. The above might be buggy :-)

_($_=" "x(1<<5)."?\n".q·/)Oo.  G°\        /
                              /\_¯/(q    /
----------------------------  \__(m.====·.(_("always off the crowd"))."·
");sub _{s./.($e="'Itrs `mnsgdq Gdbj O`qkdq")=~y/"-y/#-z/;$e.e && print}

    blog 1
    - reply 2
      - reply 4
      - reply 5
        - reply 6
    - reply 3
      - reply 7
[download]

      ancestor_id    descendant_id
      ----------------------------
          1              2
          1              3
          1              4
          1              5
          1              6
          1              7
          2              4
          2              5
          2              6
          3              7
          5              6
[download]

There are numerous patterns for expressing hierarchies, and the most effective one depends on the depth of your trees and how often that data changes. Have a look at Trees in SQL, adjacency lists and sorting. for a discussion of some of these. Also, look for anything by Joe Celko, author of Joe Celko's Trees and Hierarchies in SQL for Smarties, for example, his article Trees in SQL.

Clint

Why a separate table? This just complicates things, and to build the hierarchy out of the bunch of descendants of blog 1 you still need multiple selects.

--shmem

_($_=" "x(1<<5)."?\n".q·/)Oo.  G°\        /
                              /\_¯/(q    /
----------------------------  \__(m.====·.(_("always off the crowd"))."·
");sub _{s./.($e="'Itrs `mnsgdq Gdbj O`qkdq")=~y/"-y/#-z/;$e.e && print}

It is one way to do it. With the approach you suggested, it means going through this process:
1. fetch an entry
2. find any children
3. for each child, call step 1
This requires multiple calls to the DB, which is likely to be slower than issuing one single call to find all descendants. The process of sending the query to the DB, having the DB parse the SQL, run the query, return the results, and parsing the results has to happen many more times (depending on the nature of your data) than with the structure I suggested.

It, too, is not without its problems though. For instance, keeping the two tables in sync can be tricky. The Nested Set Model of Trees describes a way to do it in a single table, and it is very fast for reads. But inserting a node into the tree can involve a large number of updates, so, depending on your data, it may not be applicable.

I think that there is no perfect solution - each one has its pros and cons, and has to be considered in the context of the type of data (and use of that data) that you have.

Clint

Sometimes it helps to consider a problem in isolation - remove the database cruft and focus on what needs to be done to solve the issue. Consider:

use strict;
use warnings;

my %data = (
    1 => {replyTo => 0, title => 'root1'},
    2 => {replyTo => 1, title => 'Re: root1 a'},
    3 => {replyTo => 2, title => 'Re^2: root1 b'},
    4 => {replyTo => 1, title => 'Re: root1 c'},
    5 => {replyTo => 2, title => 'Re^2: root1 d'},
    6 => {replyTo => 0, title => 'root2'},
    7 => {replyTo => 6, title => 'Re: root2'},
    );

my %tree;

push @{$tree{$_->[0]}}, $_->[1] for map {[$data{$_}{replyTo}, $_]} key
+s %data;

printTree (\%data, \%tree, 0);


sub printTree {
    my ($data, $tree, $parent, $indent) = @_;
    
    $indent ||= '';
    for my $reply (sort {$a <=> $b} @{$tree{$parent}}) {
        print "$indent- $data{$reply}{title}\n";
        printTree ($data, $tree, $reply, $indent . '   ')
            if exists $tree{$reply};
    }
}
[download]

Prints:

- root1
   - Re: root1 a
      - Re^2: root1 b
      - Re^2: root1 d
   - Re: root1 c
- root2
   - Re: root2
[download]

Interestingly enough, i was recommended DBIx::Class::Tree by Matt Trout. Even more interesting is it seems to combine both shmem and clinton's ideas using a tree and a separate table for relationships as clinton mentioned.

I'm not too familiar with trees however, or this module for that matter. Any suggestions on how I might go about utiliziting it? There's some code in the docs that looks like this:

#!/usr/bin/perl
  use My::Employee;
  
  my $employee = My::Employee->create({ name=>'Matt S. Trout' });
  
  my $rs = $employee->children();
  my @siblings = $employee->children();
  
  my $parent = $employee->parent();
  $employee->parent( 7 );
[download]

It seems to me that if you add 'rootId' and 'date' fields to the 'replyTo' I used to build the tree here, then you can perform a date based fetch to get all new nodes, run through the fetched nodes to build a list of root nodes, then perform a fetch based on root node(s) to retrieve all the node information you need to build the tree.

That avoids a second table and means you perform between two and the number of root nodes referenced in the time period plus one fetches - depending on how you structure your query.

---

DWIM is Perl's answer to Gödel

If you're also using DBIx::Class, this half-pseudo code does the trick. I am running this for real (in three different versions, one is Class::DBI, two are Catalyst) in a couple production situations. Sorry I'm not posting the full working code but that would be a BIG amount of stuff including the DB and everything and I'm not quite ready to share it. I think I included most of the working bits. In has both posts that are threaded (like WordPress "pages," I think) and threaded comments belonging to the post.

So you do use two tables with this set up?

meh.

Only because the posts are threaded too. It's not necessary to have another table otherwise (well, except for users and whatever you want attached). The comments are threaded in and of themselves. It just happens to be a blog package so they are also attached to a post. If it were for a forum, they'd be standalone in their table and any without a parent would be the first in a thread.

Ok, so i've waded through the replies and decided that I'll also need to do some template fiddling.

I'll need a nested loop to retrieve the child's replies, but i'm not sure how to determine in Template::Toolkit whether a reply has a reply or not.

Some pseudo code:

<!-- REPLIES START -->
    <table>
    [% FOREACH reply IN replies -%]
      <tr>
      <td/>
      <ul> 
      <li><h2>[% reply.blog_title %]</h2>
      <p>by <a href="[% Catalyst.uri_for('/users/view/')_ reply.blog_a
+uthor %]">[% reply.blog_author %]</a> on <em>[% reply.blog_date %]</e
+m> , is a reply: [% reply.blog_is_reply %], is a draft: is a draft: [
+% reply.blog_is_draft %]</p>
     
     
      [% FILTER html_para %]
      [% reply.blog_text %]
      [% END %]
      <small>
      tags: 
      [% FOREACH tag IN reply.blog_tags.split(',') %]
      <a href="[% Catalyst.uri_for('/blog/do_search')_'?q='_ tag %]"> 
+[% tag %]</a> ,
       <p><a href="[% Catalyst.uri_for('/blog/reply/')_ reply.blog_id 
+%]">comment</a></p>
      </li>
      
      [% IF reply.has_reply %]
            <ul>
              <li>(display reply to reply stuff)</li>
            </ul>
          [% END %]
     </ul>
      [% END %]
      </small>
     
      </td>
      </tr>
      [% END %]
    </table>
    <!-- REPLIES END -->
[download]