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

[John Napiorkowski]

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.

Thanks!

John Napiorkowski









__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com