groups of groups using self joins in DBIx::Class

Question

I'm trying to understand how to use DBIx::Class.

If I want groups of records such that groups can themselves be members of groups, I might create a schema that includes something like this:

CREATE TABLE groups (
       id    INTEGER PRIMARY KEY,
       name  VARCHAR(100)
       );

CREATE TABLE group_groups (
       parent_id         INTEGER REFERENCES groups(id),
       child_id          INTEGER REFERENCES groups(id),
       PRIMARY KEY(parent_id,child_id)
       );

If I use DBIx::Class::Schema::Loader to dump this schema, I get the following relationships:

Group.pm

__PACKAGE__->has_many(
  "group_groups_children",
  "Schema::Result::GroupGroup",
  { "foreign.child_id" => "self.id" },
  { cascade_copy => 0, cascade_delete => 0 },
);

__PACKAGE__->has_many(
  "group_groups_parents",
  "Schema::Result::GroupGroup",
  { "foreign.parent_id" => "self.id" },
  { cascade_copy => 0, cascade_delete => 0 },
);

GroupGroup.pm

__PACKAGE__->belongs_to(
  "child",
  "Schema::Result::Group",
  { id => "child_id" },
  {
    is_deferrable => 1,
    join_type     => "LEFT",
    on_delete     => "CASCADE",
    on_update     => "CASCADE",
  },
);

__PACKAGE__->belongs_to(
  "parent",
  "Schema::Result::Group",
  { id => "parent_id" },
  {
    is_deferrable => 1,
    join_type     => "LEFT",
    on_delete     => "CASCADE",
    on_update     => "CASCADE",
  },
);

I think I need a many_to_many relationship bridge where the group table is on both sides, so I created this:

__PACKAGE__->many_to_many(
    'childgroups' => 'group_groups_children' , 'child');

__PACKAGE__->many_to_many(
    'parents' => 'group_groups_parents' , 'parent');

since my understanding of a the relationship bridge definition goes like this: 'accessor_name' => 'name of has_many relation in the related table', 'name of belongs_to relation in the relating table'

When I attempted this code:

my $group_rs = $schema->resultset('Group')->search(
        { id => $id }
);
my $group = $group_rs->first;
foreach my $child ($group->childgroups) {
<snip>

Setting DBIC_TRACE showed the SQL to be:

SELECT child.id, child.name FROM group_groups me 
       JOIN groups child ON child.id = me.child_id  
       WHERE ( me.child_id = ? )

But I think the line should look more like:

SELECT child.id, child.name FROM group_groups me 
       JOIN groups child ON child.id = me.child_id 
       JOIN groups parent ON parent.parent_id = me.id 
       WHERE ( me.child_id = ? )

If someone would suggest how I am misunderstanding the many_to_many relationship bridge and correct my many_to_many function definitions, I would be grateful.

Answer 1

Upon asking in #dbix-class, ribasushi helped me resolve this. Apparently, DBIC::Schema::Loader created incorrect has_many relationships in Group.pm and it should look like this:

__PACKAGE__->has_many(
  "group_groups_parents",
  "Schema::Result::GroupGroup",
  { "foreign.child_id" => "self.id" },
  { cascade_copy => 0, cascade_delete => 0 },
);

__PACKAGE__->has_many(
  "group_groups_children",
  "Schema::Result::GroupGroup",
  { "foreign.parent_id" => "self.id" },
  { cascade_copy => 0, cascade_delete => 0 },
);

The original:

has_many (group_groups_children => "Schema::Result::GroupGroup", { "foreign.child_id" => self.id" } )

means "all the rows that have my id in child_id" and really it's the parents that have my id in their child.id, not the children.

My schema was good, my relationship definitions were good, my usage code was good, it was just that the autogenerated relationships were not and my understanding was not sufficient to discover the failing.

ribasushi hinted that named constraints (i.e "better" DDL for my schema might have resulted in DBICSL getting it right.