CODEWITHSUNDEEP
mysqlhierarchical-dataadjacency-list
Herker
Herker

Reputation: 582

Adjacency list model duplicate parent & children

Hierarchical Data in MySQL Using the Adjacency List Model

I have this table named node_structur_data

CREATE TABLE node_structure_data (
   id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   title VARCHAR(455) NOT NULL,
   parent_id INT(10) UNSIGNED DEFAULT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (parent_id) REFERENCES node_structure_data (id)
   ON DELETE CASCADE ON UPDATE CASCADE
);

Output:

id  title   parent_id
1   Division  NULL
2   Site 1    1
3   Paper     2
4   ms1       3

How can I duplicate a node and its children?
For example Site 1
The id & parent_id should be unique but the title should stay the same.

Expected Output:

id  title   parent_id
1   Division  NULL
2   Site 1    1
3   Paper     2
4   ms1       3
5   Site 1    1
6   Paper     5
7   ms1       6

Upvotes: 0

Views: 271

Answers (1)

ggordon
ggordon

Reputation: 10035

The following approach first estimates the new max and then uses a recursive cte to find all children of the desired node 'Site 1' and determine their new possible parent_id if there were no other concurrent writes to the table.

I would recommend running the following in a transaction and locking the table during the operation to prevent concurrent table modifications.

To test this approach I added some additional sample data which I have included below, however you may see the approach in a demo with your initial sample data here

See output of working db fiddle below:

Schema (MySQL v8.0)

CREATE TABLE node_structure_data (
   id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   title VARCHAR(455) NOT NULL,
   parent_id INT(10) UNSIGNED DEFAULT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (parent_id) REFERENCES node_structure_data (id)
   ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO node_structure_data
  (`id`, `title`, `parent_id`)
VALUES
  ('1', 'Division', NULL),
  ('2', 'Site 1', '1'),
  ('3', 'Paper', '2'),
  ('4', 'ms1', '3'),
  ('5', 'ms2', '3'),
  ('6', 'os1', '4'),
  ('7', 'os2', '4'),
  ('8', 'gs1', '1'),
  ('9', 'hs1', '3'),
  ('10','js1','9');

Query #1

select 'Before Insert';
Before Insert
Before Insert

Query #2

select * from node_structure_data;
id title parent_id
1 Division
2 Site 1 1
3 Paper 2
4 ms1 3
5 ms2 3
6 os1 4
7 os2 4
8 gs1 1
9 hs1 3
10 js1 9

Query #3

select 'Possible Data Changes';
Possible Data Changes
Possible Data Changes

Query #4

with recursive max_id AS (
    SELECT MAX(id) as id FROM node_structure_data
),
child_nodes AS (
    SELECT
        n.id,
        title,
        parent_id,
        m.id+1 as new_id,
        parent_id as new_parent_id
    FROM
        node_structure_data n
    CROSS JOIN
        max_id as m
    WHERE
        title='Site 1'
    
  
    UNION ALL
  
    SELECT
        n.id,
        n.title,
        n.parent_id,
        @row_num:=IF(@row_num=0,c.new_id,0) + 1 + @row_num  as new_id,
        c.new_id
    FROM
        child_nodes c
    INNER JOIN
        node_structure_data n ON n.parent_id = c.id 
    CROSS JOIN (
        SELECT @row_num:=0 as rn
    ) as vars
    
)
SELECT * FROM child_nodes;
id title parent_id new_id new_parent_id
2 Site 1 1 11 1
3 Paper 2 12 11
4 ms1 3 13 12
5 ms2 3 14 12
9 hs1 3 15 12
6 os1 4 16 13
7 os2 4 17 13
10 js1 9 18 15

Query #5 - Performing actual insert

INSERT INTO node_structure_data (title,parent_id)
with recursive max_id AS (
    SELECT MAX(id) as id FROM node_structure_data
),
child_nodes AS (
    SELECT
        n.id,
        title,
        parent_id,
        m.id+1 as new_id,
        parent_id as new_parent_id
    FROM
        node_structure_data n
    CROSS JOIN
        max_id as m
    WHERE
        title='Site 1'
    
  
    UNION ALL
  
    SELECT
        n.id,
        n.title,
        n.parent_id,
        @row_num:=IF(@row_num=0,c.new_id,0) + 1 + @row_num  as new_id,
        c.new_id
    FROM
        child_nodes c
    INNER JOIN
        node_structure_data n ON n.parent_id = c.id 
    CROSS JOIN (
        SELECT @row_num:=0 as rn
    ) as vars
    
)
SELECT title,new_parent_id FROM child_nodes ORDER BY new_id;

There are no results to be displayed.

Query #6

select 'AFTER INSERT';
AFTER INSERT
AFTER INSERT

Query #7

select * from node_structure_data;
id title parent_id
1 Division
2 Site 1 1
3 Paper 2
4 ms1 3
5 ms2 3
6 os1 4
7 os2 4
8 gs1 1
9 hs1 3
10 js1 9
11 Site 1 1
12 Paper 11
13 ms1 12
14 ms2 12
15 hs1 12
16 os1 13
17 os2 13
18 js1 15

View on DB Fiddle

Let me know if this works for you.

Upvotes: 1

Related Questions