[Dbix-class] many_to_many and polymorphism

[Jess Robinson]

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