Suppose I have a person class. (The SQL is for MySQL but should be obvious.)

package Person;
use base 'MyCDBI';
__PACKAGE__->table('people');
__PACKAGE__->columns(All => qw(id name));

# CREATE TABLE people (
#   id INT NOT NULL auto_increment,
#   name VARCHAR(255) NOT NULL,
#   PRIMARY KEY (id),
#   UNIQUE (name)
# );
[download]

Now I want to record a tree of parents and children. I think the best way to do that is a table like so:

package Person::Relationship;
use base 'MyCDBI';
__PACKAGE__->table('people_tree');
__PACKAGE__->columns(Primary => qw(parent child));
__PACKAGE__->has_a(parent => 'Person');
__PACKAGE__->has_a(child => 'Person');

# CREATE TABLE people_tree (
#   parent int NOT NULL REFERENCES people,
#   child int NOT NULL REFERENCES people,
#   PRIMARY KEY (parent,child)
# );
[download]

My question is: How do I add this relationship to the Person class in a sensible way? Ideally I could use something like

Person->has_many(children => [ Person::Relationship => child ]);
Person->has_many(parents => [ Person::Relationship => parent ]);
[download]

But then CDBI doesn't know which column to use to identify "self". That is, I really need something like,

Person->has_many({ self_id => parent }, children => [ Person::Relation
+ship => child ]);
Person->has_many({ self_id => child }, parents => [ Person::Relationsh
+ip => parent ]);
[download]

Any suggestions?