Parent child relationships

= SUMMARY =

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.

= SQL =

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

= Perl = 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.

= Notes = 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.