I've had a similar problem, where I needed to construct a tree structure based on a a database table. The difference is that in my case a node could have only one parent.

I ended up by writing a module with methods like the ones you mention, but my parent method would obviously return a scalar rather than a list.

All the SQL code is isolated in this module, and my children method looks like the following.

use Win32::ODBC;
sub getChildren {
    my $self = shift;
    my $query = "SELECT ForestID ".
    "FROM Forest WHERE Parent=".$self->{ 'Id' }.
    " AND Tree='".$self->{ 'Type' }."'";

    undef $self->{ 'Children' };
    unless( $database->Sql( $query ) ) {
    while( $database->FetchRow() ) {
        push( @{$self->{'Children'}}, $database->Data("ForestID"));
    }
    }else {
    return;
    }
    return $self->{ 'Children' };
}
[download]

My parent method works in the same way, but returns the key rather than a list of keys. The table that stores the tree structure is defined as follows:

CREATE TABLE Forest (
        ForestId   INT NOT NULL,
        Parent     INT,
        Type       CHAR( 50 ) NOT NULL,
        PRIMARY KEY( ForestID, Type ));
[download]

Where ForestId is a unique key, Parent points to a ForestId and Type says which Tree we are talking about (our application can have several trees). Other methods of the module use the type information to construct SQL-queries to retrieve information from the different tables where the raw data is stored.

I have neither used Class::DBI nor Class::DBI::Join, so I am unable to help on this area. Thus my answer to your question is: No, I don't know of a neat and tidy method to do this, so I coded my SQL by hand. That may not be the best way, though.