Self-referencing tables

From ClassDBI

I found what I was looking for in this thread:

http://groups.kasei.com/mail/arc/cdbi-talk/2003-11/msg00128.html

Basically, you have a table that references itself:

CREATE TABLE employee (
  id int(10) unsigned not null auto_increment primary key,
  name varchar(255) not null,
  manager int(10) unsigned -- points back to id in another record in this table
);

In your class you would define a has_a like so:

package Employee;
...
__PACKAGE__->has_a( manager => __PACKAGE__ );

And then use it like so:

if ( my $manager = Employee->retrieve(42)->manager ) {
  print $manager->name, "\n";
}

You can also add this:

__PACKAGE__->has_many( underlings => __PACKAGE__ );

Then you can find all the employees that are managed by a particular employee:

for my $underling ( Employee->retrieve(42)->underlings ) {
   print $underling->name, "\n";
}

Note: the cascading delete mechanism then works the same as in ordinary relations: deleting an employee also deletes all his underlings! This may not be what you want.