From ClassDBI

Contents 1 SUMMARY 2 SQL 3 Perl 4 Notes

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.