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

[John Napiorkowski]

----- Original Message ----
From: Jess Robinson <castaway at desert-island.me.uk>
To: dbix-class at lists.rawmode.org
Sent: Wednesday, May 2, 2007 4:25:53 AM
Subject: Re: [Dbix-class] Many To Many on the same table trouble




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

create_related is part of my workaround.  Since I'm isolating this in a business logic module I can
deal with it but just very curious and wondering if this is something that will bite me big time at
a later date.

Here's the SQL for $subject->add_to_objects($object)

INSERT INTO subjects_objects (object_id)

So I have to do like $subject->add_to_objects($object, {subject_id=>$subject->id) to make it work correctly:

INSERT INTO subjects_tags_objects (object_id, subject_id)

remove_from is even stranger.  When I do:

$subject->remove_from_object($object)

I get;

DELETE FROM subjects_objects WHERE ( ( ( object_id = ? ) AND ( object_id = ? ) ) )

Where the first parameter is the object_id but the second is the subject_id.  There's no workaround
that I could find for this.

When I do ->relationship_info on the relationships in question I don't see anything different from
my other more convention many to manys.  So my guess is that it's something to do with the
role names.  I'll play with it a bit more later in the week to see if I can isolate an exact testable
condition if nobody has any other ideas.

Thanks!

__John


_______________________________________________
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/dbix-class@lists.rawmode.org/




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