M3ubUmnh

1-- Database schemas for SpicaCMS --
2DROP TABLE IF EXISTS categories;
3CREATE TABLE categories (
4  cat_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
5  cat_name VARCHAR(255) NOT NULL,
6  cat_left INT UNSIGNED NOT NULL,
7  cat_right INT UNSIGNED NOT NULL,
8  PRIMARY KEY (cat_id)
9);
10INSERT INTO categories VALUES (NULL, 'CEO', 1, 16);
11INSERT INTO categories VALUES (NULL, 'Senior managers', 2, 15);
12INSERT INTO categories VALUES (NULL, 'Technical team leader', 3, 6);
13INSERT INTO categories VALUES (NULL, 'Technical team', 4, 5);
14INSERT INTO categories VALUES (NULL, 'Sales team leader', 7, 10);
15INSERT INTO categories VALUES (NULL, 'Sales team', 8, 9);
16INSERT INTO categories VALUES (NULL, 'Customer service team leader', 11, 14);
17INSERT INTO categories VALUES (NULL, 'Customer service team', 12, 13);
18
19-- Gets all children of a parent node
20SELECT c1.* FROM categories c1
21JOIN categories c2 
22ON c1.cat_left > c2.cat_left AND c1.cat_left < c2.cat_right
23WHERE c2.cat_name = 'Senior managers';
24
25-- Gets immediate children of a parent node
26SELECT node.cat_name, (COUNT(parent.cat_name) - (sub_tree.depth + 1)) AS depth
27FROM categories AS node,
28    categories AS parent,
29    categories AS sub_parent,
30    (
31        SELECT node.cat_name, (COUNT(parent.cat_name) - 1) AS depth
32        FROM categories AS node,
33        categories AS parent
34        WHERE node.cat_left >= parent.cat_left AND node.cat_right <= parent.cat_right  
35        AND node.cat_name = 'Senior managers'
36        GROUP BY node.cat_name
37        ORDER BY node.cat_left
38    ) AS sub_tree
39WHERE node.cat_left >= parent.cat_left AND node.cat_right < parent.cat_right  
40AND node.cat_left > sub_parent.cat_left AND node.cat_right < sub_parent.cat_right
41AND sub_parent.cat_name = sub_tree.cat_name
42GROUP BY node.cat_name
43HAVING depth < 1
44ORDER BY node.cat_left;
45
46-- Gets parent nodes of a child node
47SELECT c2.* FROM categories c1
48JOIN categories c2 
49ON c2.cat_left < c1.cat_left AND c2.cat_right > c1.cat_right
50WHERE c1.cat_name = 'Sales team'
51ORDER BY c2.cat_left ASC;
52
53-- Gets the immediate parent of a child node
54SELECT c2.* FROM categories c1
55JOIN categories c2 
56ON c2.cat_left < c1.cat_left 
57AND c2.cat_right > c1.cat_right
58WHERE c1.cat_name = 'Sales team'
59ORDER BY c2.cat_left DESC LIMIT 0, 1;
60
61SELECT * FROM categories
62WHERE cat_left = (
63	SELECT MAX(c2.cat_left) FROM categories c1
64	JOIN categories c2 
65	ON c2.cat_left < c1.cat_left 
66	AND c2.cat_right > c1.cat_right
67	WHERE c1.cat_name = 'Sales team'
68)
69
70-- Count child nodes
71SELECT CONVERT((cat_right - cat_left - 1)/2, UNSIGNED INTEGER) AS node_count FROM categories 
72WHERE cat_name = 'Senior managers';
73
74-- Calculate depth 
75SELECT node.cat_name, (COUNT(parent.cat_name) - 1) AS depth  
76FROM categories AS node, categories AS parent  
77WHERE node.cat_left >= parent.cat_left AND node.cat_right <= parent.cat_right  
78GROUP BY node.cat_id  
79ORDER BY node.cat_left;