[Dbix-class] Better way? Transparent child/parent relationships

[Mike Friedman]

Greetings,

I have a tree of categories that looks like this:

CREATE TABLE `categories` (
  `id`          int(11)       NOT NULL auto_increment,
  `name`        varchar(255)  NOT NULL,
  `description` text          NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `category_map` (
  `id`          int(11)       NOT NULL auto_increment,
  `child`       int(11)       NOT NULL,
  `parent`      int(11)       NOT NULL,
  PRIMARY KEY  (`id`),
  FOREIGN KEY  (`child`)      REFERENCES `categories` (`id`),
  FOREIGN KEY  (`parent`)     REFERENCES `categories` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



I found this structure a lot easier than dealing with a self-joining
table, even though my application has no need for multiple parents
(not yet, anyway.) However, this means that when I call ->children on
a Category object, I get back a list of CategoryMaps instead of other
Categories. So to make this easier I did the following in my
Categories class:


__PACKAGE__->has_many( _parents  => 'MyThing::DB::CategoryMap', 'child' );
__PACKAGE__->has_many( _children => 'MyThing::DB::CategoryMap', 'parent' );

sub children {
    my $self = shift;
    my @kids = $self->_children;

    return map { $_->child } @kids;
}

sub parent {
    my $self = shift;
    my @parents = $self->_parents;

    # only allow one parent for now
    return $parents[0]->parent;
}


This seems to work just fine. However, I wonder if there are any
gotchas about this structure that I haven't considered, and in
particular, I wonder if there is a standard DBIC-ish way to accomplish
this which I have overlooked.



Mike