Parent child relationships
This is an example of a multiple parent/child relationship.
The scenario is that you have a table of hosts and you want to define which are related to each other. Each host can have multiple parents and multiple children.
This is the database setup (in Mysql):
CREATE TABLE hosts ( id int AUTO_INCREMENT, name varchar(32) NOT NULL, ip varchar(32) NOT NULL, PRIMARY KEY (id) ) TYPE=InnoDB
CREATE TABLE parents ( hostid int, parentid int, PRIMARY KEY (hostid, parentid), INDEX (hostid), FOREIGN KEY (hostid) REFERENCES hosts(id), INDEX (parentid), FOREIGN KEY (parentid) REFERENCES hosts(id) ) TYPE=InnoDB
This is the extract of the Class::DBI modules:
package Host; __PACKAGE__->table("hosts"); __PACKAGE__->columns( Primary => qw/id/, ); __PACKAGE__->columns( Essential => qw/name ip/, ); __PACKAGE__->has_many(parents => [ "Parent" => 'parentid' ], "hostid" ); __PACKAGE__->has_many(children => [ "Parent" => 'hostid' ], "parentid" );
package Parent; use strict; __PACKAGE__->table("parents"); __PACKAGE__->columns( Primary => qw/hostid parentid/, ); __PACKAGE__->has_a(hostid => 'Host'); __PACKAGE__->has_a(parentid => 'Host');
The main trick is the 2nd parameter for has_many which specifies which column of the Parent class refers to the current class (I think this didn't work properly for Class::DBI v0.96, but works fine for v3.0.7)
Now if you run $host->parents, you will get a list of parent hosts, and similarly for $host->children.
If you use Class::DBI::Loader (for v0.22), it will come back with an error:
parent method already exists in Hosts at /usr/local/share/perl/5.8.4/Class/DBI/Relationship/HasMany.pm line 15
So I think you have to define this relationship manually.