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
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/[email protected]/
