Cascading delete with many-to-many
Please help: Many to Many and cascading delete
Here is a scenario that I can't seem to work around: let say there is a new table Influences that tracks musical relationships between Artists:
create table Influences ( iid integer not_null auto_increment, influencer_artistid integer, influencee_artistid integer, primary key(iid) );
package Music::Influences; use base 'Music::MusicDB'; Music::Artist->table('Influences'); Music::Artist->columns(All => qw/iid influencer_artistid influencee_artistid/); Music::Artist->has_a(influencer_artistid => "Music::Artist"); Music::Artist->has_a(influencee_artistid => "Music::Artist");
Then, in Music::Artist add:
Music::Artist->has_many(influences => 'Music::Influences');
So suppose you have following:
ArtistA artistid = 1 ArtistB artistid = 2 ArtistC artistid = 3 ArtistD artistid = 4
and the Influences table looks like:
Deleting ArtistC will only result in the row matching iid=3 being deleted, not all three rows.
Any work arounds, suggestions, or help for this one? I really want to use Cdbi but this is a show stopper for me. Thanks...
Solution found at Perl Monks
See the discussion at PerlMonks
The solution at PerlMonks seems rather complex. Surely all you need is another has_many definition:
Music::Artist->has_many(influences => 'Music::Influences', 'influencer_id'); + Music::Artist->has_many(influencers => 'Music::Influences', 'influencee_id');
Then, each of the relationships will be deleted in turn.
This then looks like a parent/child relationship