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.