Class::DBI doesn't do what you wish by itself. (or... i haven't figured it out either).

but writing your own isn't that bad.

package Node;
...
__PACKAGE__->set_sql('children',
  'SELECT child_id FROM Relations WHERE parent_id = ?',
  'Main');

sub children {
  my $self = shift;
  my $sth = $self->sql_children;
  $sth->execute($self->id);
  my @children;
  my $row;
  while ($row = $sth->fetchrow_arrayref) {
    push @children, __PACKAGE__->retrieve($row->[0]);
  }
  return (@children);
}
[download]

and do about the same for parents.

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.

package Node;
use base 'My::Class::DBI';

... # standard Class::DBI stuff here

# Declare that we have a relationships table that
# tells us where our children are
Node->has_many('children_refs' => 'Relationship' => 'parent_id');

# Now that we can fetch those records from the
# relationships table, we need to have a way to
# get Node objects from those relationships
sub children {
  return Node->search_in(
    Node->primary_column => [
      map { $_->child_id } shift->children_refs
    ]
  );
}

# Similarly for parents
[download]

package My::Class::DBI;
use base 'Class::DBI';

sub search_in {
  my $class = shift;
  my %args = ref $_[0] eq 'HASH' ? ${ $_[0] } : @_;

  # Normalize columns
  my @columns = keys %args;
  $_ = $class->_normalized($_) for @columns;

  # Check columns
  $class->_check_columns(@columns);

  # Keep list of all values (for each column)
  # that'll be used in the IN clause -- we'll
  # later bind these values to an SQL statement
  # so that the DBI can take care of the quoting
  my @values;
  my $sql = join ' AND ', map {
    # Grab values for IN clause
    my @vals = @{ $args{$_} };
    push @values, @vals;
    
    my $in_str = join(',', ('?') x @vals);
    "$_ IN ($in_str)" if @vals;
  } @columns;
 
  return unless @values;
  return $class->retrieve_from_sql($sql, @values);
}
[download]

With the exception of the search_in method, this just might be a nice and tidy solution. I especially prefer this strategy as it doesn't rely on hand-coded SQL satements.

Good luck!
David