· 8 years ago · Apr 06, 2018, 08:10 AM
1CREATE TABLE list_relation
2 (
3 parent_id INT UNSIGNED NOT NULL,
4 child_id INT UNSIGNED NOT NULL,
5
6 UNIQUE(parent_id, child_id)
7
8 FOREIGN KEY (parent_id)
9 REFERENCES list (id)
10 ON DELETE CASCADE,
11
12 FOREIGN KEY (child_id)
13 REFERENCES list (id)
14 ON DELETE CASCADE
15 );
16
17CREATE TABLE `list_relation` (
18 `child_id` int unsigned NOT NULL,
19 `parent_id` int unsigned NOT NULL,
20 PRIMARY KEY (`child_id`,`parent_id`)
21);
22insert into list_relation (child_id, parent_id) values
23 (2,1),
24 (3,1),
25 (4,2),
26 (4,3),
27 (5,3);
28
29set @new_child_id = 1;
30set @new_parent_id = 4;
31
32with recursive rcte as (
33 select *
34 from list_relation r
35 where r.child_id = @new_parent_id
36 union all
37 select r.*
38 from rcte
39 join list_relation r on r.child_id = rcte.parent_id
40)
41select * from rcte
42
43child_id | parent_id
44 4 | 2
45 4 | 3
46 2 | 1
47 3 | 1
48
49select * from rcte where parent_id = @new_child_id limit 1
50
51select exists (select * from rcte where parent_id = @new_child_id)
52
53set @new_child_id = 4;
54set @new_parent_id = 1;
55
56with recursive rcte as (
57 select *
58 from list_relation r
59 where r.child_id = @new_child_id
60 union all
61 select r.*
62 from rcte
63 join list_relation r on r.child_id = rcte.parent_id
64)
65select exists (select * from rcte where parent_id = @new_parent_id)
66
67set @list = 4;
68
69with recursive rcte (list_id) as (
70 select @list
71 union distinct
72 select r.parent_id
73 from rcte
74 join list_relation r on r.child_id = rcte.list_id
75)
76select distinct i.*
77from rcte
78join item i on i.list_id = rcte.list_id
79
80CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_list_relation_recursive`(
81 in itemId text,
82 in iPreserve text,
83 out oResult text
84
85)
86BEGIN
87
88 DECLARE ChildId text default null;
89
90 IF (coalesce(itemId,'') = '') then
91 -- when no id received retun whatever we have in the preserve container
92 set oResult = iPreserve;
93 ELSE
94 -- add the received id to the preserving container
95 SET iPreserve = concat_ws(',',iPreserve,itemId);
96 SET oResult = iPreserve;
97
98 SET ChildId =
99 (
100 coalesce(
101 (
102 Select
103 group_concat(TNode.child_id separator ',') -- get all children
104 from
105 list_relation as TNode
106 WHERE
107 not find_in_set(TNode.child_id, iPreserve) -- if we don't already have'em
108 AND find_in_set(TNode.parent_id, itemId) -- from these parents
109 )
110 ,'')
111 );
112
113 IF length(ChildId) >0 THEN
114 -- one or more child found, recursively search again for further child elements
115 CALL sp_list_relation_recursive(ChildId,iPreserve,oResult);
116 END IF;
117
118 END IF;
119
120 -- uncomment this to see the progress looping steps
121 -- select ChildId,iPreserve,oResult;
122END
123
124SET MAX_SP_RECURSION_DEPTH = 250;
125set @list = '';
126call test.sp_list_relation_recursive(1,'',@list);
127select @list;
128
129+----------------+
130| @list |
131+----------------+
132| ,1,2,3,6,4,4,5 |
133+----------------+
134
135CREATE DEFINER=`root`@`localhost` FUNCTION `fn_list_relation_recursive`(
136 NodeId int
137) RETURNS text CHARSET utf8
138 READS SQL DATA
139 DETERMINISTIC
140BEGIN
141
142 /*
143 Returns a tree of nodes
144 branches out all possible branches
145 */
146 DECLARE mTree mediumtext;
147 SET MAX_SP_RECURSION_DEPTH = 250;
148
149
150 call sp_list_relation_recursive(NodeId,'',mTree);
151
152 RETURN mTree;
153END
154
155SELECT
156 *,
157 FN_LIST_RELATION_RECURSIVE(parent_id) AS parents_children
158FROM
159 list_relation;
160
161+----------+-----------+------------------+
162| child_id | parent_id | parents_children |
163+----------+-----------+------------------+
164| 1 | 7 | ,7,1,2,3,6,4,4,5 |
165| 2 | 1 | ,1,2,3,6,4,4,5 |
166| 3 | 1 | ,1,2,3,6,4,4,5 |
167| 4 | 2 | ,2,4 |
168| 4 | 3 | ,3,4,5 |
169| 5 | 3 | ,3,4,5 |
170| 6 | 1 | ,1,2,3,6,4,4,5 |
171| 51 | 50 | ,50,51 |
172+----------+-----------+------------------+
173
174insert into list_relation (child_id,parent_id)
175select
176 -- child, parent
177 1,6
178where
179 -- parent not to be foud in child's children node
180 not find_in_set(6,fn_list_relation_recursive(1));
181
182SELECT t1.list_id, t2.list_id, t3.list_id
183FROM list AS t1
184LEFT JOIN list as t2 ON t2.parent_id = t1.list_id
185LEFT JOIN list as t3 ON t3.parent_id = t2.list_id
186WHERE t1.list_id = #your_list_id#
187
188CREATE TABLE parent_list (
189 list_id INT UNSIGNED NOT NULL,
190 parent_list_id INT UNSIGNED NOT NULL,
191 PRIMARY KEY (list_id, parent_list_id)
192);
193
194SELECT * FROM parent_list
195WHERE list_id = potential_parent_id AND parent_list_id = potential_child_id;
196
197CREATE PROCEDURE 'inherit'(
198IN in_parent_id INT UNSIGNED,
199IN in_child_id INT UNSIGNED
200)
201BEGIN
202 DECLARE result INT DEFAULT 0;
203
204 DECLARE EXIT HANDLER FOR SQLEXCEPTION
205 BEGIN
206 ROLLBACK;
207 SELECT -1;
208 END;
209
210 START TRANSACTION;
211
212 IF EXISTS(SELECT * FROM parent_list WHERE list_id = in_parent_id AND parent_list_id = in_child_id) THEN
213 SET result = 1; -- just some error code
214 ELSE
215 -- do your inserting here
216
217 -- update parent_list
218 INSERT INTO parent_list (SELECT in_child_id, parent_list_id FROM parent_list WHERE list_id = in_parent_id);
219 INSERT INTO parent_list VALUES (in_child_id, in_parent_id);
220 END IF;
221
222 COMMIT;
223 SELECT result;
224END
225
226SELECT
227 a.parent_id,
228 a.child_id
229FROM list_relation a
230JOIN list_relation b
231ON a.child_id = b.parent_id AND a.parent_id = b.child_id