On Tue, 9 Jan 2007, Jason Gottshall wrote:
I'm trying to figure out how to set up a polymorphic relationship using
many_to_many, and I'm having a bit of trouble. Assume 4 tables:
Actors: id, name
ActorRoles: actor_id, charactername, production_id, production_type
Films: id, title
TVShows: id, title
where ActorRoles.production_type is one of "film" or "tv" and
ActorRoles.production_id is the foreign key in either the Films or
TVShows table. So I've got this:
package My::DBIC::Schema::Actor;
__PACKAGE__->has_many( actor_roles =>
'My::DBIC::Schema::ActorRole',
{ 'foreign.actor_id' => 'self.id' } );
__PACKAGE__->many_to_many( films =>
'actor_roles',
'film' );
__PACKAGE__->many_to_many( tvshows =>
'actor_roles',
'tvshow' );
package My::DBIC::Schema::ActorRole;
__PACKAGE__->belongs_to( actor =>
'My::DBIC::Schema::Actor',
{ 'foreign.id' => 'self.actor_id' );
__PACKAGE__->belongs_to( film =>
'My::DBIC::Schema::Film',
{ 'foreign.id' => 'self.production_id' } );
__PACKAGE__->belongs_to( tvshow =>
'My::DBIC::Schema::TVShow',
{ 'foreign.id' => 'self.production_id' } );
package My::DBIC::Schema::Film;
__PACKAGE__->has_many( actor_roles =>
'My::DBIC::Schema::ActorRole',
{ 'foreign.production_id' => 'self.id' } );
__PACKAGE__->many_to_many( actors =>
'actor_roles',
'actor' );
package My::DBIC::Schema::TVShow;
__PACKAGE__->has_many( actor_roles =>
'My::DBIC::Schema::ActorRole',
{ 'foreign.production_id' => 'self.id' } );
__PACKAGE__->many_to_many( actors =>
'actor_roles',
'actor' );
The problem is that I haven't accounted for the production_type in the
relationships between Films/TVShows and ActorRoles. As long as the union
of all film and tvshow ids is unique, my results are accurate. But that
makes me nervous. I've searched the DBIC docs and and list archives and
haven't yet come across anything that helps. Ideas?
In theory, it would be sane to do:
__PACKAGE__->belongs_to( film =>
'My::DBIC::Schema::Film',
{ 'foreign.id' => 'self.production_id',
'film' => 'self.production_type' } );
.. but in practice DBIC doesnt support that sort of join yet..
I'd solve it by creating a table of productiontypes, and then adding:
__PACKAGE__->belongs_to( production_type =>
'My::DBIC::Schema::ProductionTypes',
{ 'foreign.id' => 'self.production_type' } );
__PACKAGE__->belongs_to( film =>
'My::DBIC::Schema::Film',
{ 'foreign.id' => 'self.production_id' },
{ join => 'My::DBIC::Schema::ProductionTypes' } );
.. which will always add a one-to-one join to productiontypes when you
search via film, and thus restrict to the type in the production_type
field.
.. (I hope, untested ;)
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]/
