Discussion :

[lmplmp]

Bonjour,

J'ai récemment lu le tuto sur le sujet de ces arbres et j'ai trouvé cette méthode très intéressante mais un point me bloque: l'affichage.

Le but étant de générer un arbre de ce style:

|-|Parent de l'arbre entier 1
|--|Personne 1.1
|---|Personne 1.1.1
|----|Personne 1.1.1.1
|----|Personne 1.1.1.2
|--|Personne 1.2
|---|Personne 1.2.1
|--|Personne 1.3

Mes données proviennent de deux tables:

id_personne, id_pere, bd, bg

id_personne, nom

Dans une autre discussion sur ce forum, j'ai trouvé les indications suivantes que je peine à parfaitement comprendre et donc à mettre en œuvre dans mon cas précis. Peut être est-ce aussi du fait que j'ai deux tables au lieu d'une.

Pour cette vue, il te faut :

1/ trier les éléments dans l'ordre de l'arbre
2/ connaître la profondeur de chaque élément
3/ concaténer les numéros d'ordre de chaque élément avec ceux de ses ancêtres

Le 1/ est facile, il suffit de faire ORDER BY BordGauche.
Pour le 2/, il faut faire une auto-jointure pour retrouver les pères de chaque élément et les compter.
Pour le 3/, on peut utiliser la même auto-jointure et effectuer un GROUP_CONCAT (propre à MySQL, mais très pratique !).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT COUNT(*) AS Prof,   A.Titre,  GROUP_CONCAT(A.NumOrdre ORDER BY A.BordGauche SEPARATOR '.')
FROM arbre A
INNER JOIN arbre A2 
    ON A.BordGauche <= A2.BordGauche AND A.BordDroit >= A2.BordDroit
GROUP BY A.ID
ORDER BY A.BordGauche

Pour ce qui est de mes difficultés:

> 1: Je comprend.
> 2: Je ne saisi pas vraiment comment se déroule cette partie de la requête.
> 3: De ce que je comprend la requête récupère les numéros de catégorie/id à travers NumOrdre et les concatène. Pour ma part mes données ne sont pas aussi simple et j'aimerais que ce numéro soit généré à la volée, sachant que le 1 est forcément le sommet de l'arbre (cf joli dessin au dessus).

> Existe t il une alternative à GROUP_CONCAT ? (J'ai l'impression que ce n'est pas forcément très propre?)


Merci d'avance pour votre aide.

[GeekMokona]

Comme vu avec toi sur le Chat ! comme tu utilise MariaDB

il faut que tu passe par une CTE récursive pour constitué ton arbre hiérarchique en retournant une profondeur hiérarchique (entier) et une chaine de caractère représentant le chemin Hierarchique ( ex: '1' , '1.2.1' )

La profondeur va te servir a écrire le nombre de tiré nécessaire via les fonctions Replicat et concat
Le Chemin a trier .

[lmplmp]

Mon dernier essai suivant tes conseils:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
WITH dynasties AS (
 SELECT * FROM Liaison_dynastie_maison WHERE id_maison = 0
 
  UNION ALL
 
 
   SELECT * FROM Liste_maison_dynastie LM 
    INNER JOIN dynasties
    ON LM.id_dynastie = dynasties.id_lmd
 
)
SELECT * FROM dynasties

Je ne saisi toujours pas très bien le concept...

[Artemus24]

Salut à tous.

J'ai récemment lu le tuto sur le sujet de ces arbres ...

De quels arbres parlez-vous ? Sont-ce des arbres de la théorie des graphes ?
Car le terme arbre est trop vague pour être parfaitement compris.

Mes données proviennent de deux tables:

id_personne, id_pere, bd, bg

id_personne, nom

Si vous désirez une organisation arborescente, il faut revoir vos deux tables, afin de les fusionner en une seule !
A priori, votre organisation arborescente est de type hiérarchique.

il faut que tu passe par une CTE récursive pour constitué ton arbre hiérarchique

Le CTE (Common Table Expressions) n'existe pas sous MySql, contrairement à Microsoft SQL Server.
Soit vous vous êtes trompé de forum car vous êtes ici dans le forum mysql, soit votre propos est hors MySql et n'a pas lieu d'être.

Pour résoudre ce problème en MySql, il faut le simuler en se servant d'une procédure stockée, que voici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `travail`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `travail`
( `rang`    integer unsigned NOT NULL auto_increment primary key,
  `lib`     varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS `parent`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `parent`
( `id`      integer unsigned NOT NULL auto_increment primary key,
  `lien`    integer unsigned     NULL,
  `nom`     varchar(255)     NOT NULL,
  CONSTRAINT `FK_lien` FOREIGN KEY (`lien`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `parent` (`lien`,`nom`) values
  (null, ''),           ( 1, 'grand-père 1'), ( 1, 'grand-père 2'),  ( 1, 'grand-père 3'),
  ( 2,   'père 1-1'),   ( 2, 'père 1-2'),     ( 2, 'père 1-3'),
  ( 3,   'père 2-1'),   ( 3, 'père 2-2'),     ( 3, 'père 2-3'),
  ( 4,   'père 3-1'),   ( 4, 'père 3-2'),     ( 4, 'père 3-3'),
  ( 5,   'fils 1-1-1'), ( 5, 'fils 1-1-2'),
  ( 6,   'fils 1-2-1'), ( 6, 'fils 1-2-2'),   ( 6, 'fils 1-2-3'),
  ( 7,   'fils 1-3-1'), ( 7, 'fils 1-3-2'),   ( 7, 'fils 1-3-3'),
  ( 8,   'fils 2-1-1'), ( 8, 'fils 2-1-2'),
  ( 9,   'fils 2-2-1'), ( 9, 'fils 2-2-2'),   ( 9, 'fils 2-2-3'),
  (10,   'fils 2-3-1'), (10, 'fils 2-3-2'),   (10, 'fils 1-3-3'),
  (11,   'fils 3-1-1'), (11, 'fils 3-1-2'),   (12, 'fils 3-2-1'),
  (12,   'fils 3-2-2'), (12, 'fils 3-2-3'),
  (13,   'fils 3-3-1'), (13, 'fils 3-3-2'),   (13, 'fils 3-3-3')
--------------
 
--------------
select * from parent order by lien, id
--------------
 
+----+------+--------------+
| id | lien | nom          |
+----+------+--------------+
|  1 | NULL |              |
|  2 |    1 | grand-père 1 |
|  3 |    1 | grand-père 2 |
|  4 |    1 | grand-père 3 |
|  5 |    2 | père 1-1     |
|  6 |    2 | père 1-2     |
|  7 |    2 | père 1-3     |
|  8 |    3 | père 2-1     |
|  9 |    3 | père 2-2     |
| 10 |    3 | père 2-3     |
| 11 |    4 | père 3-1     |
| 12 |    4 | père 3-2     |
| 13 |    4 | père 3-3     |
| 14 |    5 | fils 1-1-1   |
| 15 |    5 | fils 1-1-2   |
| 16 |    6 | fils 1-2-1   |
| 17 |    6 | fils 1-2-2   |
| 18 |    6 | fils 1-2-3   |
| 19 |    7 | fils 1-3-1   |
| 20 |    7 | fils 1-3-2   |
| 21 |    7 | fils 1-3-3   |
| 22 |    8 | fils 2-1-1   |
| 23 |    8 | fils 2-1-2   |
| 24 |    9 | fils 2-2-1   |
| 25 |    9 | fils 2-2-2   |
| 26 |    9 | fils 2-2-3   |
| 27 |   10 | fils 2-3-1   |
| 28 |   10 | fils 2-3-2   |
| 29 |   10 | fils 1-3-3   |
| 30 |   11 | fils 3-1-1   |
| 31 |   11 | fils 3-1-2   |
| 32 |   12 | fils 3-2-1   |
| 33 |   12 | fils 3-2-2   |
| 34 |   12 | fils 3-2-3   |
| 35 |   13 | fils 3-3-1   |
| 36 |   13 | fils 3-3-2   |
| 37 |   13 | fils 3-3-3   |
+----+------+--------------+
--------------
DROP PROCEDURE IF EXISTS `arbre`
--------------
 
--------------
CREATE PROCEDURE `arbre` ( In _noeud integer unsigned,
                           In _pref  char (20) )
DETERMINISTIC
NO SQL
BEGIN
  DECLARE _id    INTEGER      DEFAULT 0;
  DECLARE _lien  INTEGER      DEFAULT 0;
  DECLARE _nom   VARCHAR(255) DEFAULT '';
 
  DECLARE _fin   INTEGER      DEFAULT 1;
  DECLARE _tab   CURSOR FOR SELECT id, lien, nom FROM `parent` WHERE lien = _noeud;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET _fin = 0;
 
  OPEN  _tab;
  FETCH _tab INTO _id, _lien, _nom;
 
  WHILE (_fin)
  DO
    INSERT INTO `travail` (`lib`) VALUES (concat(_pref, '... ', _nom));
 
    CALL arbre(_id, concat('..', _pref));
 
    FETCH _tab INTO _id, _lien, _nom;
  END WHILE;
 
  CLOSE _tab;
END
--------------
 
--------------
truncate `travail`
--------------
 
--------------
call `arbre` (1, '')
--------------
 
--------------
select lib from travail
--------------
 
+--------------------+
| lib                |
+--------------------+
| ... grand-père 1   |
| ..... père 1-1     |
| ....... fils 1-1-1 |
| ....... fils 1-1-2 |
| ..... père 1-2     |
| ....... fils 1-2-1 |
| ....... fils 1-2-2 |
| ....... fils 1-2-3 |
| ..... père 1-3     |
| ....... fils 1-3-1 |
| ....... fils 1-3-2 |
| ....... fils 1-3-3 |
| ... grand-père 2   |
| ..... père 2-1     |
| ....... fils 2-1-1 |
| ....... fils 2-1-2 |
| ..... père 2-2     |
| ....... fils 2-2-1 |
| ....... fils 2-2-2 |
| ....... fils 2-2-3 |
| ..... père 2-3     |
| ....... fils 2-3-1 |
| ....... fils 2-3-2 |
| ....... fils 1-3-3 |
| ... grand-père 3   |
| ..... père 3-1     |
| ....... fils 3-1-1 |
| ....... fils 3-1-2 |
| ..... père 3-2     |
| ....... fils 3-2-1 |
| ....... fils 3-2-2 |
| ....... fils 3-2-3 |
| ..... père 3-3     |
| ....... fils 3-3-1 |
| ....... fils 3-3-2 |
| ....... fils 3-3-3 |
+--------------------+
--------------
truncate `travail`
--------------
 
--------------
set @id = (select id from parent where nom like 'grand-père 2')
--------------
 
--------------
call `arbre` (@id, '')
--------------
 
--------------
select lib from travail
--------------
 
+------------------+
| lib              |
+------------------+
| ... père 2-1     |
| ..... fils 2-1-1 |
| ..... fils 2-1-2 |
| ... père 2-2     |
| ..... fils 2-2-1 |
| ..... fils 2-2-2 |
| ..... fils 2-2-3 |
| ... père 2-3     |
| ..... fils 2-3-1 |
| ..... fils 2-3-2 |
| ..... fils 1-3-3 |
+------------------+
--------------
truncate `travail`
--------------
 
--------------
set @id = (select id from parent where nom like 'père 3-2')
--------------
 
--------------
call `arbre` (@id, '')
--------------
 
--------------
select lib from travail
--------------
 
+----------------+
| lib            |
+----------------+
| ... fils 3-2-1 |
| ... fils 3-2-2 |
| ... fils 3-2-3 |
+----------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

[lmplmp]

Bonjour, effectivement je fonctionne avec MariaDB. Merci pour votre procédure (et la réponse =) mais cela me semble bien plus complexe que de la récurrence. Est-ce plus lent/gourmand ?

D'accord pour la restructuration de la table, je vais faire cela pour me retrouver avec ce que vous me conseillez.

Pour l'arbre il s'agit d'un arbre N-aire. On a une souche qu'on désigne par un id, et il y a N1 branches pour cette souche puis Nn pour chacune des branches, etc...
En fait, imaginez cela comme un arbre généalogique de descendance, il peut donc y avoir beaucoup d'enfants comme aucun.

[Artemus24]

Salut lmplmp.

effectivement je fonctionne avec MariaDB.

Ce forum est consacré à MySql.
Je sais que tout ce qui concerne la récursivité n'existe pas sous MySql, sauf en la simulant comme je l'ai fait.

Pour ce qui est de MariaDB, je ne sais pas s'il existe des fonctionnalités comme le CTE, absent de MySql.

mais cela me semble bien plus complexe que de la récurrence.

Vous vouliez dire plus lourd à écrire. Oui, car j'ai dû simuler un comportement récursif.
Plus complexe non, car c'est identique à ce que vous cherchez à faire.

D'accord pour la restructuration de la table, je vais faire cela pour me retrouver avec ce que vous me conseillez.

L'organisation de la table est indépendante de l'outil que vous allez utiliser.

Ici, j'ai fait le choix de lier les lignes à partir de la colonne "lien".
Le lien est un pointeur qui associe une ligne "fille" vers une ligne "mère".
Donc on peut mettre de zéro à N lignes "fille", pointant toutes vers une seule ligne "mère".

Pour l'arbre il s'agit d'un arbre N-aire.

Dans une définition mathématique, un arbre N-aire est un arbre qui possède plusieurs descendants.
Autrement dit, plusieurs enfants rattachés à un seule mère.

Dans le modèle relationnel, la représentation que j'ai choisi de faire, est de type hiérarchique.

@+