Trois premiers enfants d'une relation récursive

**PLJerem** · 18/10/2023, 23h52

Bonjour,

Je dispose de deux tables :
- arbo
- travaux

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
 
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 :

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
 
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

**Waldar** · 19/10/2023, 11h58

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.

**PLJerem** · 19/10/2023, 15h52

Bonjour Walda,

Merci pour le routage du message.

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

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
 
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.

**Artemus24** · 14/11/2023, 14h10

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 mysql :

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
--------------
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.

@+

**PLJerem** · 15/11/2023, 12h41

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 :

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
 
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

**escartefigue** · 15/11/2023, 14h50

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

Code SQL :

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

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
[. . .]

Trois premiers enfants d'une relation récursive

SQL Oracle

Discussions similaires

Partager

Partager