Construction d'une hiérarchie via 2 triggers
Bonjour,
Après pas mal de recherches sur le net, en sachant que la récursivité dans les requêtes avec mysql n'existe pas, je viens donner une solution pour créer un arbre sur une table.
construction de la table :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
| DROP TABLE IF EXISTS categories;
CREATE TABLE IF NOT EXISTS categories (
id int(11) NOT NULL AUTO_INCREMENT,
parent_id int(11) DEFAULT NULL,
level text,
categories_name varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY cat (id,parent_id),
KEY IDX_3AF34668727ACA70 (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE categories
ADD CONSTRAINT FK_3AF34668727ACA70 FOREIGN KEY (parent_id) REFERENCES categories (id) ON DELETE CASCADE ON UPDATE CASCADE; |
la hiérarchie est assurée par la colonne level.
ce sont deux triggers qui alimente cette colonne :
1, au moment où on créé une catégorie :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| CREATE TRIGGER `CreateLevel` BEFORE INSERT ON `categories`
FOR EACH ROW BEGIN
SET @parent_level = (SELECT level FROM categories WHERE id = NEW.parent_id);
SET @next = (SELECT MAX(id) FROM categories);
CREATE TEMPORARY TABLE hierarchie
(
temp_level text
);
INSERT INTO hierarchie(temp_level)value(@parent_level);
if @next is null THEN
SET @next = 1;
ELSE
SET @next = @next + 1;
END IF;
SET @level = (SELECT temp_level from hierarchie);
if @level is null THEN
SET NEW.level = LPAD(@next,5,'0');
ELSE
SET NEW.level = concat(@level,'.',LPAD(@next,5,'0'));
END IF;
END |
1 où on met à jour la catégorie :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| CREATE TRIGGER `UpdateLevel` BEFORE UPDATE ON `categories`
FOR EACH ROW BEGIN
SET @parent_level = (SELECT level FROM categories WHERE id = NEW.parent_id);
CREATE TEMPORARY TABLE hierarchie
(
temp_level text
);
INSERT INTO hierarchie(temp_level)value(@parent_level);
SET @level = (SELECT temp_level from hierarchie);
if @level is null THEN
SET NEW.level = LPAD(OLD.id,5,'0');
ELSE
SET NEW.level = concat(@level,'.',LPAD(OLD.id,5,'0'));
END IF;
END |
une requête sans tri :
Code:
SELECT id,parent_id,categories_name FROM `categories`;
http://farm3.staticflickr.com/2820/9...b6699d20_o.png
une requête avec tri :
Code:
SELECT id,parent_id,categories_name,categories_title,level FROM categories ORDER BY ABS(level)
http://farm3.staticflickr.com/2810/9...46ea0ac5_o.png
j'ai volontairement ré-incrémenté par deux fois la table pour tester diverses situations.
c'est pas le code du siècle, mais il reste fonctionnel.
nb : oui,je sais, il existe cela aussi : Managing Hierarchical Data in MySQL, un poil plus tarabiscoté.
