[Dbix-class] many_to_many and polymorphism

[Jason Gottshall]

On Wednesday, January 10, 2007, Jess Robinson wrote:

> 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..

Yeah, that's what I thought. I had already tried it that way, and sure
enough DBIx::Class::ResultSource::resolve_condition() choked with
"Invalid rel cond key film". I'd love to provide a patch to enable
literal values for filtering in join conditions, but I'm not yet
familiar enough with the DBIC internals. If someone can point me to the
sub(s) where the conditions are actually converted to SQL, I can toy
around with it...
 
> 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 ;)

The current practical issue is that I have a given actor, and want to
fetch all related films and tvshows, which seems to circumvent the
solution you proposed (please correct me if I'm wrong). Fortunately for
me, the ids for films and shows come from the same unique sequence, so
the joins to the two different tables manage to find the right records
without caring what the type is. Phew!

Unfortunately, I anticipate running into this same issue with other
polymorphic relations elswhere in our system. The tables and data are
legacy, and I'm just trying to wrap DBIC around them. I'm betting
there's at least one situation where the type-related tables' PKs are
drawn from independent sequences, at which point I'm going to need to
find some way to get this working the "right" way.

Thanks for your input, Jess. If you got any other suggestions, I'm all
ears!

Jason