Cascading delete with many-to-many
From ClassDBI
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:
| iid | influencer_id | influencee_id |
| 1 | 1 | 3 |
| 2 | 2 | 3 |
| 3 | 3 | 4 |
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
ALTERNATIVE ANSWER
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

