jgallagher has asked for the wisdom of the Perl Monks concerning the following question:

I'm not exactly sure how to go about doing this. I'm hoping that there is a CDBI way to do it; I'm thinking there probably isn't. How do you implement a many-to-many relationship of a table with itself?

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?

Replies are listed 'Best First'.
Re: Class::DBI and a self-referencing many-to-many
by merlyn (Sage) on Sep 14, 2005 at 14:54 UTC
    In my Class::DBI-based link checker, I solved the problem like this: 
    {
  package My::Link;
  our @ISA = qw(My::DBI);

  __PACKAGE__->table('link');
  __PACKAGE__->columns(Primary => qw(src dst));
  __PACKAGE__->has_a(src => 'My::Page');
  __PACKAGE__->has_a(dst => 'My::Page');
}

{
  package My::Page;
  our @ISA = qw(My::DBI);

  __PACKAGE__->table('page');
  __PACKAGE__->columns(All => qw(location state last_status
                                 last_checked last_good last_modified)
+);

  __PACKAGE__->has_many(inbound => 'My::Link', 'dst', { sort => 'src' 
+});
  __PACKAGE__->has_many(outbound => 'My::Link', 'src', { sort => 'dst'
+ });
}

    [download]
    It looks close to what you did. Not sure why mine worked and yours didn't.

    -- Randal L. Schwartz, Perl hacker
    Be sure to read my standard disclaimer if this is a reply.

[reply]
[d/l]

      That's pretty much identical to what I did. I even get an identical error. :-)

      My test code was:

      my $page1 = My::Page->create({ location => 'abc' });
my $page2 = $page1->add_to_inbound({ location => 'def' });

      [download]

      The error I get:

      location is not a column of My::Link at /usr/lib/perl5/site_perl/5.8.6/Class/DBI/Relationship/HasMany.pm line 92

      This is with Class::DBI 3.04.

      Update:

      Maybe this is just a problem with the auto-created add_to_* method. If I:

      my $page1 = My::Page->create({ location => 'abc' });
my $page2 = My::Page->create({ location => 'def' });

My::Link->create({src => $page1, dst => $page2});

croak Dumper([$page1->inbound()],
             [$page1->outbound()],
             [$page2->inbound()],
             [$page2->outbound()],
             );

      [download]

      I almost get what I expect. Should $page1->inbound() return a My::Link or an array of My::Pages?

[reply]
[d/l]
[select]
Re: Class::DBI and a self-referencing many-to-many
by perrin (Chancellor) on Sep 14, 2005 at 16:59 UTC
    Class::DBI supports what you are asking for here, but you have the syntax worng. Set up your relationships like this: 
    Person->has_many(children => [ Person::Relationship => child ], 'paren
+t');
Person->has_many(parents => [ Person::Relationship => parent ], 'child
+');

    [download]
    In my opinion though, it would be better to just add a single parent_id column to your person table and use a simple tree structure. Adding a separate table complicates some things.
[reply]
[d/l]
      I have a couple of questions. First, regarding that syntax. This means (if I understand CDBI correctly) that I need to use my classes as so: 
      my $parent1 = Person->create({ name => 'P1' });
my $parent2 = Person->create({ name => 'P2' });

my $child = Person->create({ name => 'C1' });

# This seems like a cumbersome way to set up the relationships
$child->add_to_parents({ parent => $parent1 });
$child->add_to_parents({ parent => $parent2 });

# Or, alternatively
# $parent1->add_to_children({ child => $child });
# $parent2->add_to_children({ child => $child });

      [download]

      Is there any way to sucessfully set those relationships when I'm constructing the objects?

      Also, I thought of adding a single parent_id column to the person table, but wouldn't that prevent a given person from having multiple parents?

[reply]
[d/l]
        Your syntax for adding parents looks correct, or you could just call create() on your linking table directly. What is it that you don't like about it? Do you have an example of alternative syntax you'd like to use?

        You're right that my parent_id column idea would mean only a single parent. I was thinking of a tree in computer science terms, where a node can only have one parent. What you want is probably a directed graph rather than a tree.

[reply]

Back to Seekers of Perl Wisdom