xJzk8hPn

1DROP PROCEDURE IF EXISTS getAllCategoriesRecursive;
2
3DELIMITER $$
4CREATE PROCEDURE getAllCategoriesRecursive (IN parentID INT)
5LANGUAGE SQL
6NOT DETERMINISTIC
7SQL SECURITY INVOKER
8
9BEGIN
10
11DECLARE newCategoriesCount INT;
12
13CREATE TEMPORARY TABLE allCategories (uid INT);
14CREATE TEMPORARY TABLE newCategories (uid INT);
15CREATE TEMPORARY TABLE nextNewCategories (uid INT);
16
17INSERT INTO allCategories SELECT parentID;
18INSERT INTO newCategories SELECT parentID;
19
20REPEAT
21
22   INSERT INTO nextNewCategories
23   SELECT DISTINCT uid
24      FROM tx_dam_cat
25      WHERE
26         parent_id IN (SELECT uid FROM newCategories) AND
27         uid NOT IN (SELECT uid FROM allCategories);
28   INSERT INTO allCategories SELECT uid FROM nextNewCategories;
29   SELECT COUNT(*) INTO newCategoriesCount FROM nextNewCategories;
30   ALTER TABLE newCategories RENAME tmpTbl;
31   ALTER TABLE nextNewCategories RENAME newCategories;
32   ALTER TABLE tmpTbl RENAME nextNewCategories;
33   TRUNCATE TABLE nextNewCategories;
34
35UNTIL newCategoriesCount = 0 END REPEAT;
36
37SELECT uid FROM allCategories;
38
39END$$
40
41DELIMITER ;
42
43
44CALL getAllCategoriesRecursive(3)