SQLite chain delete from table-CodePudding

I have a situation where I have 2 tables, 'mots' and 'mots_followers_bridge'. There is one relevant row in mots called 'mot_id' and two rows in mots_followers_bridge, 'parent_id' and 'follower_id. These can set up as the following:

CREATE TABLE 'mots' (mot_id TEXT NOT NULL, PRIMARY KEY('mot_id'));

CREATE TABLE 'mots_followers_bridge' (
  parent_id TEXT NOT NULL, 
  follower_id TEXT NOT NULL,
  FOREIGN KEY('parent_id') REFERENCES 'mots'('mot_id'),
  FOREIGN KEY('follower_id') REFERENCES 'mots('mot_id'),
  PRIMARY KEY('parent_id', 'follower_id'));

The above setup allows for 'chains' of mots to be set with the future ability to have multiple possible following mots for each mot. So, for example, I could have:

table: mots

| mot_id |
| :----- |
| MOT_1  |
| MOT_2  |
| MOT_3  |

table: mots_followers_bridge:

| parent_id | follower_id |
| :-------- | :---------- |
| MOT_1     | MOT_2       |
| MOT_2     | MOT_3       |

My question relates to when I need to delete MOT_1. If I want to delete all mots in the chain, i.e. delete MOT_2 and MOT_3, what would be a way to do this?

Of course I could create a loop where I start with MOT_1, delete all rows in mots_followers_bridge where it is a parent, while marking down all follower_id's then go through all follower_id's, noting all their followers then deleting all the rows where they are parents, then repeating this. However, this seems very resource intensive as I have to make a bunch of database calls.

Is there some way that I can combine this chain of deletes into a single database call?

CodePudding user response：

Declare the cascade delete for the foreign keys:

  FOREIGN KEY('parent_id') REFERENCES mots('mot_id') ON DELETE CASCADE,
  FOREIGN KEY('follower_id') REFERENCES mots('mot_id') ON DELETE CASCADE,

Turn foreign keys on:

PRAGMA FOREIGN_KEYS = ON;

Use a recursive CTE to delete the chain:

WITH RECURSIVE del(n) AS (
    SELECT 'MOT1'
    UNION
    SELECT follower_id FROM mots_followers_bridge AS mfb
                       JOIN del ON del.n = mfb.parent_id
) DELETE FROM mots WHERE mot_id IN del;