· 8 years ago · Mar 19, 2018, 02:12 AM
1id | name | parent_id
219 | category1 | 0
320 | category2 | 19
421 | category3 | 20
522 | category4 | 21
6......
7
8select id,
9 name,
10 parent_id
11from (select * from products
12 order by parent_id, id) products_sorted,
13 (select @pv := '19') initialisation
14where find_in_set(parent_id, @pv)
15and length(@pv := concat(@pv, ',', id))
16
17with recursive cte (id, name, parent_id) as
18(
19 select id,
20 name,
21 parent_id
22 from products
23 where parent_id = 19
24 union all
25 select p.id,
26 p.name,
27 p.parent_id
28 from products p
29 inner join cte
30 on p.parent_id = cte.id
31)
32select * from cte;
33
34ID | NAME
3519 | category1
3619/1 | category2
3719/1/1 | category3
3819/1/1/1 | category4
39
40select id,
41 name
42from products
43where id like '19/%'
44
45select p6.parent_id as parent6_id,
46 p5.parent_id as parent5_id,
47 p4.parent_id as parent4_id,
48 p3.parent_id as parent3_id,
49 p2.parent_id as parent2_id,
50 p1.parent_id as parent_id,
51 p1.id as product_id,
52 p1.name
53from products p1
54left join products p2 on p2.id = p1.parent_id
55left join products p3 on p3.id = p2.parent_id
56left join products p4 on p4.id = p3.parent_id
57left join products p5 on p5.id = p4.parent_id
58left join products p6 on p6.id = p5.parent_id
59where 19 in (p1.parent_id,
60 p2.parent_id,
61 p3.parent_id,
62 p4.parent_id,
63 p5.parent_id,
64 p6.parent_id)
65order by 1, 2, 3, 4, 5, 6, 7;
66
67+-------------+----------------------+--------+
68| category_id | name | parent |
69+-------------+----------------------+--------+
70| 1 | ELECTRONICS | NULL |
71| 2 | TELEVISIONS | 1 |
72| 3 | TUBE | 2 |
73| 4 | LCD | 2 |
74| 5 | PLASMA | 2 |
75| 6 | PORTABLE ELECTRONICS | 1 |
76| 7 | MP3 PLAYERS | 6 |
77| 8 | FLASH | 7 |
78| 9 | CD PLAYERS | 6 |
79| 10 | 2 WAY RADIOS | 6 |
80+-------------+----------------------+--------+
81
82SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
83FROM category AS t1
84LEFT JOIN category AS t2 ON t2.parent = t1.category_id
85LEFT JOIN category AS t3 ON t3.parent = t2.category_id
86LEFT JOIN category AS t4 ON t4.parent = t3.category_id
87WHERE t1.name = 'ELECTRONICS';
88
89+-------------+----------------------+--------------+-------+
90| lev1 | lev2 | lev3 | lev4 |
91+-------------+----------------------+--------------+-------+
92| ELECTRONICS | TELEVISIONS | TUBE | NULL |
93| ELECTRONICS | TELEVISIONS | LCD | NULL |
94| ELECTRONICS | TELEVISIONS | PLASMA | NULL |
95| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
96| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL |
97| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |
98+-------------+----------------------+--------------+-------+
99
100select @pv:=category_id as category_id, name, parent from category
101join
102(select @pv:=19)tmp
103where parent=@pv
104
105category_id name parent
10619 category1 0
10720 category2 19
10821 category3 20
10922 category4 21
110
111-- --------------------------------------------------------------------------------
112-- Routine DDL
113-- Note: comments before and after the routine body will not be stored by the server
114-- --------------------------------------------------------------------------------
115DELIMITER $$
116
117CREATE DEFINER=`root`@`localhost` FUNCTION `get_lineage`(the_id INT) RETURNS text CHARSET utf8
118 READS SQL DATA
119BEGIN
120
121 DECLARE v_rec INT DEFAULT 0;
122
123 DECLARE done INT DEFAULT FALSE;
124 DECLARE v_res text DEFAULT '';
125 DECLARE v_papa int;
126 DECLARE v_papa_papa int DEFAULT -1;
127 DECLARE csr CURSOR FOR
128 select _id,parent_id -- @n:=@n+1 as rownum,T1.*
129 from
130 (SELECT @r AS _id,
131 (SELECT @r := table_parent_id FROM table WHERE table_id = _id) AS parent_id,
132 @l := @l + 1 AS lvl
133 FROM
134 (SELECT @r := the_id, @l := 0,@n:=0) vars,
135 table m
136 WHERE @r <> 0
137 ) T1
138 where T1.parent_id is not null
139 ORDER BY T1.lvl DESC;
140 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
141 open csr;
142 read_loop: LOOP
143 fetch csr into v_papa,v_papa_papa;
144 SET v_rec = v_rec+1;
145 IF done THEN
146 LEAVE read_loop;
147 END IF;
148 -- add first
149 IF v_rec = 1 THEN
150 SET v_res = v_papa_papa;
151 END IF;
152 SET v_res = CONCAT(v_res,'-',v_papa);
153 END LOOP;
154 close csr;
155 return v_res;
156END
157
158select get_lineage(the_id)
159
160SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
161SELECT @pv:=(SELECT GROUP_CONCAT(id SEPARATOR ',') FROM table WHERE parent_id IN (@pv)) AS lv FROM table
162JOIN
163(SELECT @pv:=1)tmp
164WHERE parent_id IN (@pv)) a;
165
166ancestor | descendant | depth
1670 | 0 | 0
1680 | 19 | 1
1690 | 20 | 2
1700 | 21 | 3
1710 | 22 | 4
17219 | 19 | 0
17319 | 20 | 1
17419 | 21 | 3
17519 | 22 | 4
17620 | 20 | 0
17720 | 21 | 1
17820 | 22 | 2
17921 | 21 | 0
18021 | 22 | 1
18122 | 22 | 0
182
183SELECT cat.* FROM categories_closure AS cl
184INNER JOIN categories AS cat ON cat.id = cl.descendant
185WHERE cl.ancestor = 20 AND cl.depth > 0
186
187DROP TABLE IF EXISTS category;
188CREATE TABLE category (
189 id INT AUTO_INCREMENT PRIMARY KEY,
190 name VARCHAR(20),
191 parent_id INT,
192 CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
193 REFERENCES category (id)
194) engine=innodb;
195
196INSERT INTO category VALUES
197(19, 'category1', NULL),
198(20, 'category2', 19),
199(21, 'category3', 20),
200(22, 'category4', 21),
201(23, 'categoryA', 19),
202(24, 'categoryB', 23),
203(25, 'categoryC', 23),
204(26, 'categoryD', 24);
205
206DROP PROCEDURE IF EXISTS getpath;
207DELIMITER $$
208CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
209BEGIN
210 DECLARE catname VARCHAR(20);
211 DECLARE temppath TEXT;
212 DECLARE tempparent INT;
213 SET max_sp_recursion_depth = 255;
214 SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent;
215 IF tempparent IS NULL
216 THEN
217 SET path = catname;
218 ELSE
219 CALL getpath(tempparent, temppath);
220 SET path = CONCAT(temppath, '/', catname);
221 END IF;
222END$$
223DELIMITER ;
224
225DROP FUNCTION IF EXISTS getpath;
226DELIMITER $$
227CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC
228BEGIN
229 DECLARE res TEXT;
230 CALL getpath(cat_id, res);
231 RETURN res;
232END$$
233DELIMITER ;
234
235SELECT id, name, getpath(id) AS path FROM category;
236
237+----+-----------+-----------------------------------------+
238| id | name | path |
239+----+-----------+-----------------------------------------+
240| 19 | category1 | category1 |
241| 20 | category2 | category1/category2 |
242| 21 | category3 | category1/category2/category3 |
243| 22 | category4 | category1/category2/category3/category4 |
244| 23 | categoryA | category1/categoryA |
245| 24 | categoryB | category1/categoryA/categoryB |
246| 25 | categoryC | category1/categoryA/categoryC |
247| 26 | categoryD | category1/categoryA/categoryB/categoryD |
248+----+-----------+-----------------------------------------+
249
250SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%';
251
252+----+-----------+-----------------------------------------+
253| id | name | path |
254+----+-----------+-----------------------------------------+
255| 20 | category2 | category1/category2 |
256| 21 | category3 | category1/category2/category3 |
257| 22 | category4 | category1/category2/category3/category4 |
258+----+-----------+-----------------------------------------+
259
260select @pv:=id as id, name, parent_id
261from products
262join (select @pv:=19)tmp
263where parent_id=@pv
264
265id name parent_id
26620 category2 19
26721 category3 20
26822 category4 21
26926 category24 22
270
271select
272 @pv:=p1.id as id
273 , p2.name as parent_name
274 , p1.name name
275 , p1.parent_id
276from products p1
277join (select @pv:=19)tmp
278left join products p2 on p2.id=p1.parent_id -- optional join to get parent name
279where p1.parent_id=@pv
280
281select id,
282 name,
283 parent_id
284from (select * from products
285 order by parent_id, id) products_sorted,
286 (select @pv := '19') initialisation
287where find_in_set(parent_id, @pv) > 0
288and @pv := concat(@pv, ',', id)
289
290select a.id,if(a.parent = 0,@varw:=concat(a.id,','),@varw:=concat(a.id,',',@varw)) as list from (select * from recursivejoin order by if(parent=0,id,parent) asc) a left join recursivejoin b on (a.id = b.parent),(select @varw:='') as c having list like '%19,%';
291
292<?php
293require '/path/to/vendor/autoload.php'; $db = new PDO(...); // Set up your database connection
294$stm = $db->query('SELECT id, parent, title FROM tablename ORDER BY title');
295$records = $stm->fetchAll(PDO::FETCH_ASSOC);
296$tree = new BlueMTree($records);
297...
298
299is_related(id, parent_id);
300
301is_related(21, 19) == 1;
302is_related(20, 19) == 1;
303is_related(21, 18) == 0;
304
305select ...
306from table t
307join table pt on pt.id in (select i.id from table i where is_related(t.id,i.id));
308
309id | name | path
31019 | category1 | /19
31120 | category2 | /19/20
31221 | category3 | /19/20/21
31322 | category4 | /19/20/21/22
314
315-- get children of category3:
316SELECT * FROM my_table WHERE path LIKE '/19/20/21%'
317-- Reparent an item:
318UPDATE my_table SET path = REPLACE(path, '/19/20', '/15/16') WHERE path LIKE '/19/20/%'
319
320// base10 => base36
321 '1' => '1',
322 '10' => 'A',
323 '100' => '2S',
324 '1000' => 'RS',
325 '10000' => '7PS',
326 '100000' => '255S',
327 '1000000' => 'LFLS',
328 '1000000000' => 'GJDGXS',
329 '1000000000000' => 'CRE66I9S'
330
331SELECT id,NAME,'' AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 WHERE prent is NULL
332UNION
333SELECT b.id,a.name,b.name AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id WHERE a.prent is NULL AND b.name IS NOT NULL
334UNION
335SELECT c.id,a.name,b.name AS subName,c.name AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id WHERE a.prent is NULL AND c.name IS NOT NULL
336UNION
337SELECT d.id,a.name,b.name AS subName,c.name AS subsubName,d.name AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id LEFT JOIN Table1 AS d ON d.prent=c.id WHERE a.prent is NULL AND d.name IS NOT NULL
338ORDER BY NAME,subName,subsubName,subsubsubName