[Dbix-class] Many To Many on the same table trouble

[Jess Robinson]

On Tue, 1 May 2007, John Napiorkowski wrote:

> Hi,
>
> I have a table that joins to itself with a many to many condition.  My problem is that I can't seem
> to figure out how to map the 'role names' I'm using in the joining table.  I have a workaround but
> I am sure there is some way to make this work properly.
>
> Here's my table:
>
> Resources
> -------------------------
> resource_id     (PK)
>
>
> SubjectsObjects
> --------------------------
> subject_id (FK to Resources.resource_id)
> object_id (FK to Resources.resource_id)
>
> Primary KEY is (subject_id, object_id)
>
> And I'm making my Classes like:
>
> ##----------------------------------------------------------------
> package MyApp::Schema::Resources;
> ##----------------------------------------------------------------
>
>
> __PACKAGE__->table('Resources');
> __PACKAGE__->add_columns( 'resource_id');
> __PACKAGE__->->set_primary_key("resource_id");
>
> __PACKAGE__->has_many(
>  "subobj_subjects",
>  "MyApp::Schema::SubjectsObjects",
>  { "foreign.subject_id" => "self.resource_id" },
> );
> __PACKAGE__->has_many(
>  "subobj_objects",
>  "MyApp::Schema::SubjectsObjects",
>  { "foreign.object_id" => "self.resource_id" },
> );
>
> __PACKAGE__->many_to_many('subjects', 'subobj_subjects', 'subject');
> __PACKAGE__->many_to_many('objects', 'subobj_objects', 'object');
>
>
> ##----------------------------------------------------------------
>
> package MyApp::Schema::SubjectsObjects;
>
> ##----------------------------------------------------------------
>
> __PACKAGE__->table('SubjectsObjects');
>
> __PACKAGE__->add_columns( 'subject_id','object_id');
>
> __PACKAGE__->->set_primary_key( 'subject_id','object_id');
>
> __PACKAGE__->belongs_to(
>  "subject",
>  "MyApp::Schema::Resources",
>  { "foreign.resource_id" => "self.subject_id" },
> );
>
> __PACKAGE__->belongs_to(
>  "object",
>  "MyApp::Schema::Resources",
>  { "foreign.resource_id" => "self.object_id" },
> );
>
> Now the issue I get is when I am trying to create M:M relationships.  For example if I have
> two result rows $subject and $object that I found with something like:
>
> my $subject = $schema->resultset('Resource')->find(.....);
> my $object = $schema->resultset('Resource')->find(.....);
>
> I'd think I could do like:
>
> $subject->add_to_objects($object);
>
> However this won't work.  I need to do:
>
> $subject->add_to_objects($object, {subject_id=>$subject->id});
>
> When I turn on debugging so that I can see the SQL it's trying to make I can see that in the
> first example it's not properly finding the subject_id from the way I am describing the
> relationship.
>
> I have worse trouble with ->remove_from_XXX.
>
> I figure I have to somehow name the relationship.  I tried:
>
>
>
> { "foreign.subject_id" => \"self.resource_id as subject_id" }
>
> But that didn't help.  I'm thinking that I am missing something monumentally obvious.
>
> I've tried this on the stable release on CPAN and on the current branch with the same results.
>
> If anyone has run into this or has some thoughts I would be very grateful.
>


Hmm, seems fine in general, unless I'm missing something.. Could you give 
us the SQL you get for finding/adding etc please?

(I so prefer create_related, add_to is just confusing ;)

Jess