Trois premiers enfants d'une relation récursive

Version imprimable

Bonjour,

Je dispose de deux tables :
- arbo
- travaux

Code:

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
 
CREATE TABLE `arbo` (
  `pere` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `fils` varchar(20) COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 
 
INSERT INTO `arbo` VALUES('LI1', 'TR1');
INSERT INTO `arbo` VALUES('LI2', 'TR2');
INSERT INTO `arbo` VALUES('LI2', 'TR3');
INSERT INTO `arbo` VALUES('LI3', 'TR4');
INSERT INTO `arbo` VALUES('LI3', 'TR5');
INSERT INTO `arbo` VALUES('TR1', 'VO1');
INSERT INTO `arbo` VALUES('TR1', 'VO2');
INSERT INTO `arbo` VALUES('TR1', 'VO3');
INSERT INTO `arbo` VALUES('TR2', 'VO4');
INSERT INTO `arbo` VALUES('TR2', 'VO5');
INSERT INTO `arbo` VALUES('TR2', 'VO6');
INSERT INTO `arbo` VALUES('TR3', 'VO7');
INSERT INTO `arbo` VALUES('TR3', 'VO8');
INSERT INTO `arbo` VALUES('TR3', 'VO9');
INSERT INTO `arbo` VALUES('TR4', 'VO10');
INSERT INTO `arbo` VALUES('TR4', 'VO11');
INSERT INTO `arbo` VALUES('TR4', 'VO12');
INSERT INTO `arbo` VALUES('TR5', 'VO13');
INSERT INTO `arbo` VALUES('TR5', 'VO14');
INSERT INTO `arbo` VALUES('TR5', 'VO15');
INSERT INTO `arbo` VALUES('VO1', 'EQ1');
INSERT INTO `arbo` VALUES('VO1', 'EQ2');
INSERT INTO `arbo` VALUES('VO2', 'EQ3');
INSERT INTO `arbo` VALUES('VO3', 'EQ4');
INSERT INTO `arbo` VALUES('VO3', 'EQ5');
INSERT INTO `arbo` VALUES('VO4', 'EQ6');
INSERT INTO `arbo` VALUES('VO4', 'EQ7');
INSERT INTO `arbo` VALUES('VO5', 'EQ8');
INSERT INTO `arbo` VALUES('VO5', 'EQ9');
INSERT INTO `arbo` VALUES('VO6', 'EQ10');
INSERT INTO `arbo` VALUES('VO7', 'EQ11');
INSERT INTO `arbo` VALUES('VO7', 'EQ12');
INSERT INTO `arbo` VALUES('VO8', 'EQ13');
INSERT INTO `arbo` VALUES('VO8', 'EQ14');
INSERT INTO `arbo` VALUES('VO9', 'EQ15');
INSERT INTO `arbo` VALUES('VO10', 'EQ16');
INSERT INTO `arbo` VALUES('VO10', 'EQ17');
INSERT INTO `arbo` VALUES('VO11', 'EQ18');
INSERT INTO `arbo` VALUES('VO12', 'EQ19');
INSERT INTO `arbo` VALUES('VO13', 'EQ20');
INSERT INTO `arbo` VALUES('VO15', 'EQ21');
 
CREATE TABLE `travaux` (
  `id` int NOT NULL,
  `equipement_id` varchar(20) COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 
INSERT INTO `travaux` VALUES(15, 'EQ1');
INSERT INTO `travaux` VALUES(19, 'EQ10');
INSERT INTO `travaux` VALUES(23, 'EQ11');
INSERT INTO `travaux` VALUES(21, 'EQ16');
INSERT INTO `travaux` VALUES(26, 'EQ18');
INSERT INTO `travaux` VALUES(16, 'EQ2');
INSERT INTO `travaux` VALUES(22, 'EQ3');
INSERT INTO `travaux` VALUES(18, 'TR1');
INSERT INTO `travaux` VALUES(27, 'TR2');
INSERT INTO `travaux` VALUES(20, 'TR5');
INSERT INTO `travaux` VALUES(28, 'VO11');
INSERT INTO `travaux` VALUES(24, 'VO14');
INSERT INTO `travaux` VALUES(25, 'VO15');
INSERT INTO `travaux` VALUES(17, 'VO3');

La première table "arbo" contient une arborescence qui peut aller d'un niveau à une dizaine de niveaux.

Une seconde table "travaux" qui contient des tâches à réaliser. La colonne "equipement_id" se retrouve soit dans la colonne "pere" soit dans la colonne "fils" de la table "arbo".

Mon but premier était de récupérer toutes les tâches de la table "travaux" dont la colonne "equipement_id" se retrouve par arborescence dans la table "arbo" avec un "pere" valant "LI1" ou "LI2". J'ai réussi à avoir ce résultat par la requête suivante :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SELECT DISTINCT
    t.*
FROM 
    travaux t
JOIN 
    (
        SELECT
        	pere,
        	fils
        FROM arbo b 
        START WITH PARENT IN ('LI1', 'LI2')
        CONNECT BY NOCYCLE 
            PRIOR fils = pere
    ) a
ON t.equipement_id = a.pere OR t.equipement_id = a.fils;

Je souhaite maintenant avoir les 2 premiers enfants à partir du plus grand père (à partir du "START WITH PARENT")

Si je devais modéliser le résultat ça ressemblerait à ça :

grand_pere	fils1	fils2	equipement_id	travaux_id
LI1	TR1	VO1	EQ1	15
LI1	TR1	VO1	EQ2	16
LI1	TR1	VO3	VO3	17
LI1	TR1		TR1	18
LI2	TR2	VO6	EQ10	19
LI1	TR1	VO2	EQ3	22
LI2	TR3	VO7	EQ11	23
LI1	TR2		TR2	27

Je suis preneur pour un petit coup de main, là je commence à tourner en rond :)

19/10/2023, 10h58
Waldar

Avec START WITH CONNECT BY vous avez une pseudo colonne level qui vous donne la profondeur de la récursion.
Rajoutez la dans votre select interne et filtrez dessus.

Bonjour Walda,

Merci pour le routage du message.

J'ai réécrit ma requête de la sorte :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 SELECT DISTINCT t.*, a.test_level FROM travaux t JOIN ( SELECT pere, fils, LEVEL as test_level FROM arbo b START WITH PARENT IN ('LI1', 'LI2') CONNECT BY NOCYCLE PRIOR fils = pere ) a ON t.equipement_id = a.pere OR t.equipement_id = a.fils;
En effet j'arrive bien à trouver le niveau de equipement_id dans l'arborescence.
Après je n'arrive pas à voir avec le level comment réussir à remonter aux deux premiers fils.

Salut PLJerem.

Votre sujet a été déplacé dans le mauvais forum.
Il me semble, vu l'instruction "start with parent connect by nocycle", que cela concerne Oracle et non MySql.

Si vous désirez faire de la récursivité sur vos tables, elles ne sont pas normalisées.
Pourquoi avoir créé deux colonnes "père" et "fils" ? Une seule suffit, sachant qu'un fils peut aussi être père.
Pour cela, il suffit de créer un lien allant du fils vers le père.
Cela évitera d'avoir une requête complexe qui n'a pas lieu d'être.

Code:

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
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `arbo`
--------------
 
--------------
CREATE TABLE `arbo`
( `id`      integer unsigned NOT NULL primary key,
  `parent`  varchar(255)     NOT NULL,
  `ptr`     integer unsigned     NULL,
  CONSTRAINT `FK_01` FOREIGN KEY (`ptr`) REFERENCES `arbo` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `arbo` (`id`,`parent`,`ptr`) values
  (01,'LI1',NULL),(02,'LI2',NULL),(03,'LI3',NULL),
  (04,'TR1',  01),
  (05,'TR2',  02),(06,'TR3',  02),
  (07,'TR4',  03),(08,'TR5',  03),
  (09,'VO1',  04),(10,'VO2',  04),(11,'VO3',  04),
  (12,'VO4',  05),(13,'VO5',  05),(14,'VO6',  05),
  (15,'VO7',  06),(16,'VO8',  06),(17,'VO9',  06),
  (18,'VO10', 07),(19,'VO11', 07),(20,'VO12', 07),
  (21,'VO13', 08),(22,'VO14', 08),(23,'VO15', 08),
  (24,'EQ1',  09),(25,'EQ2',  09),
  (26,'EQ3',  10),
  (27,'EQ4',  11),(28,'EQ5',  11),
  (29,'EQ6',  12),(30,'EQ7',  12),
  (31,'EQ8',  13),(32,'EQ9',  13),
  (33,'EQ10', 14),
  (34,'EQ11', 15),(35,'EQ12', 15),
  (36,'EQ13', 16),(37,'EQ14', 16),
  (38,'EQ15', 17),
  (39,'EQ16', 18),(40,'EQ17', 18),
  (41,'EQ18', 19),
  (42,'EQ19', 20),
  (43,'EQ20', 21),
  (44,'EQ21', 23)
--------------
 
--------------
select * from `arbo`
--------------
 
+----+--------+------+
| id | parent | ptr  |
+----+--------+------+
|  1 | LI1    | NULL |
|  2 | LI2    | NULL |
|  3 | LI3    | NULL |
|  4 | TR1    |    1 |
|  5 | TR2    |    2 |
|  6 | TR3    |    2 |
|  7 | TR4    |    3 |
|  8 | TR5    |    3 |
|  9 | VO1    |    4 |
| 10 | VO2    |    4 |
| 11 | VO3    |    4 |
| 12 | VO4    |    5 |
| 13 | VO5    |    5 |
| 14 | VO6    |    5 |
| 15 | VO7    |    6 |
| 16 | VO8    |    6 |
| 17 | VO9    |    6 |
| 18 | VO10   |    7 |
| 19 | VO11   |    7 |
| 20 | VO12   |    7 |
| 21 | VO13   |    8 |
| 22 | VO14   |    8 |
| 23 | VO15   |    8 |
| 24 | EQ1    |    9 |
| 25 | EQ2    |    9 |
| 26 | EQ3    |   10 |
| 27 | EQ4    |   11 |
| 28 | EQ5    |   11 |
| 29 | EQ6    |   12 |
| 30 | EQ7    |   12 |
| 31 | EQ8    |   13 |
| 32 | EQ9    |   13 |
| 33 | EQ10   |   14 |
| 34 | EQ11   |   15 |
| 35 | EQ12   |   15 |
| 36 | EQ13   |   16 |
| 37 | EQ14   |   16 |
| 38 | EQ15   |   17 |
| 39 | EQ16   |   18 |
| 40 | EQ17   |   18 |
| 41 | EQ18   |   19 |
| 42 | EQ19   |   20 |
| 43 | EQ20   |   21 |
| 44 | EQ21   |   23 |
+----+--------+------+
 
--------------
DROP TABLE IF EXISTS `trav`
--------------
 
--------------
CREATE TABLE `trav`
( `id`    integer unsigned NOT NULL primary key,
  `equi`  varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `trav` (`id`,`equi`) values
  (15,'EQ1'),(16,'EQ2'),(22,'EQ3'),(19,'EQ10'),(23,'EQ11'),(21,'EQ16'),(26,'EQ18'),
  (18,'TR1'),(27,'TR2'),(20,'TR5'),(17, 'VO3'),(28,'VO11'),(24,'VO14'),(25,'VO15')
--------------
 
--------------
select * from `trav`
--------------
 
+----+------+
| id | equi |
+----+------+
| 15 | EQ1  |
| 16 | EQ2  |
| 17 | VO3  |
| 18 | TR1  |
| 19 | EQ10 |
| 20 | TR5  |
| 21 | EQ16 |
| 22 | EQ3  |
| 23 | EQ11 |
| 24 | VO14 |
| 25 | VO15 |
| 26 | EQ18 |
| 27 | TR2  |
| 28 | VO11 |
+----+------+
 
--------------
with recursive cte (`id`,`parent`,`pere`) as
  (      select a.id, a.parent, a.parent
           from `arbo` as a
          where a.parent in ('LI1','LI2')
      union all
         select a.id, a.parent, c.pere
           from `arbo` as a
     inner join cte    as c
             on c.id = a.ptr
  )      select a.id, a.parent, a.pere, f1.parent as Fils1, f2.parent as Fils2
           from cte as a
     inner join `trav` as t
             on t.equi = a.parent
left outer join `arbo` as z
             on z.id = a.id
left outer join `arbo` as f1
             on f1.id = z.ptr
left outer join `arbo` as f2
             on f2.id = f1.ptr
--------------
 
+------+--------+------+-------+-------+
| id   | parent | pere | Fils1 | Fils2 |
+------+--------+------+-------+-------+
|   24 | EQ1    | LI1  | VO1   | TR1   |
|   25 | EQ2    | LI1  | VO1   | TR1   |
|   11 | VO3    | LI1  | TR1   | LI1   |
|    4 | TR1    | LI1  | LI1   | NULL  |
|   33 | EQ10   | LI2  | VO6   | TR2   |
|   26 | EQ3    | LI1  | VO2   | TR1   |
|   34 | EQ11   | LI2  | VO7   | TR3   |
|    5 | TR2    | LI2  | LI2   | NULL  |
+------+--------+------+-------+-------+
 
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

J'espère avoir bien compris votre demande et que cela répond à votre attente. :)

@+

Bonjour Artemus,

J'avais bien posté le sujet dans le forum Oracle, cependant un modérateur l'a déplacé dans MySQL.

Malheureusement la structure de la base de données m'est imposée et je n'ai aucune possibilité de la modifier.

Pour le résultat final, c'est quasiment ça, on serait idéalement sur ce résultat :

id parent pere Fils1 Fils2

24 EQ1 LI1 TR1 VO1

25 EQ2 LI1 TR1 VO1

11 VO3 LI1 TR1

4 TR1 LI1 TR1

33 EQ10 LI2 TR2 VO6

26 EQ3 LI1 TR1 VO2

34 EQ11 LI2 TR3 VO7

5 TR2 LI2 TR2

Je pense que si je ne me trompe pas la requête doit correspondre à ça :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 with recursive cte (`id`,`parent`,`pere`) as ( select a.id, a.parent, a.parent from `arbo` as a where a.parent in ('LI1','LI2') union all select a.id, a.parent, c.pere from `arbo` as a inner join cte as c on c.id = a.ptr ) select a.id, a.parent, a.pere, f1.parent as Fils1, f2.parent as Fils2 from cte as a inner join `trav` as t on t.equi = a.parent left outer join `arbo` as z on z.id = a.id left outer join `arbo` as f2 on f2.id = z.ptr left outer join `arbo` as f2 on f1.id = f2.ptr
Merci pour ton travail pour apporter une raison, par contre je suis obligé de rester sur ma structure de base :(

Si dans le résultat souhaité, il s'agit simplement de remplacer "null" par du blanc, utilisez l'instruction COALESCE ;)
Code:

1 2 3 4 5 6 7 8 9 10 11 select a.id , a.parent , a.pere , coalesce(f1.parent, ' ') as Fils1 , coalesce(f2.parent, ' ') as Fils2 from cte as a inner join `trav` as t on t.equi = a.parent left outer join `arbo` as z on z.id = a.id [. . .]

id	parent	pere	Fils1	Fils2
24	EQ1	LI1	TR1	VO1
25	EQ2	LI1	TR1	VO1
11	VO3	LI1	TR1
4	TR1	LI1	TR1
33	EQ10	LI2	TR2	VO6
26	EQ3	LI1	TR1	VO2
34	EQ11	LI2	TR3	VO7
5	TR2	LI2	TR2