Discussion :

[Jarod46]

Bonjour, je cherche actuellement à optimiser une fonction récursive servant en gros à récupérer tout les enfants d'une table, mes compétences en sql sont pas mal limités, j'ai passé beaucoup de temps à chercher sans solution efficace donc je tente un post, pas évident pour moi à formuler clairement.
Cette table contiens les colonnes classiques pour ce genre de cas, un ID unique, et une ID_PARENT. Jusque là ça va c'est facile, on trouve plein d'exemples, mais en plus il existe aussi deux autres colonnes contenant éventuellement une liste d'ids correspondant à une liste d'enfants (de type VARCHAR, les ids sont séparés par les virgules).
Actuellement, c'est une fonction php récursive qui fait le boulot, en faisant une nouvelle requête à itération.
Je cherche à faire le faire si possible en une seule requête, ou en tout cas améliorer autant que possible les performances.
Déjà me doutant bien que celle liste d'id sous forme de chaine charactère va forcément poser problème alors j'ai créé des tables de correspondance avec deux colonnes, une avec l'id du parent, et une avec celle de l'enfant.

La table S_WO_BILANS contiens les colonnes ID_WO et ID_WO_PARENT, la table S_WO_BILANS_PROD les colonnes ID_WO et ID_WO_ENFANT
Un exemple de que j'ai essayé et qui me semblait pas mal sur le principe :

Code :

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

1
2
3
4
5
6
7
8
9
 
 WITH wob_tree (ID_WO, ID_WO_PARENT, lvl) AS 
 (
 SELECT ID_WO, ID_WO_PARENT, 0 AS lvl FROM S_WO_BILANS WHERE ID_WO = 399852 
 UNION ALL SELECT wobn.ID_WO, wobn.ID_WO_PARENT, lvl+1 
 FROM S_WO_BILANS wobn, wob_tree wobt WHERE wobn.ID_WO_PARENT = wobt.ID_WO 
    /*OR wobn.ID_WO IN (SELECT wobp.ID_WO_ENFANT FROM S_WO_BILANS_PROD wobp WHERE wobp.ID_WO = wobt.ID_WO)*/
 ) 
 SELECT * FROM wob_tree

Dès que je rajoute la seconde condition permettant d'associer des enfants à un parent, la requête coince (boucle infinie ?)
Donc voilà je ne suis peut-être même pas sur la bonne voie, si des experts du SQL pourraient me donner une piste ce serait super, merci.

[tbc92]

Je pars du principe que tu as une table avec 2 colonnes (idParent et idEnfant), et a priori une autre table où tu as pour chaque individu des caractéristiques ( le prénom dans mon exemple).
Voici une requête qui convient :

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
with aa as ( 
select 100 as qparent, 1000 as qenfant  from dual 
union 
select 100 as qparent, 1001 as qenfant from dual 
union 
select 100 as qparent, 1002 as qenfant from dual 
union 
select 1001 as qparent, 10010 as qenfant from dual 
union 
select 1001 as qparent, 10011 as qenfant from dual 
union
select 1002 as qparent, 10020 as qenfant from dual 
)
, ab as ( 
select 100 as id, 'xx100' as prenom from dual
union
select 1000 as id, 'xx1000' as prenom from dual
union
select 1001 as id, 'xx1001' as prenom from dual
union
select 1002 as id, 'xx1002' as prenom from dual
union
select 10010 as id, 'xx10010' as prenom from dual
union
select 10011 as id, 'xx10011' as prenom from dual
union
select 10020 as id, 'xx10020' as prenom from dual
)
, ac as ( 
select qparent, qenfant , level
from aa 
connect by prior qenfant = qparent
start with qparent = 100 
)
select ac.* , ab1.prenom , ab2.prenom   from ac , ab ab1, ab ab2  where
 ab1.id =  ac.qparent 
and  ab2.id =  ac.qenfant

C'est connect by prior et Start With qui gère la récurcivité : on cherche les lignes telles que qParent soit égal à 'prior Qenfant' , c'est à dire les lignes où qParent vaut le qEnfant des lignes déjà récupérées.
Et Start With dit qu'on cherche tous les descendants de tel individu.
Level permet de savoir si la ligne récupérée est de génération 1, 2 ... n ( = nombre d'intermédiaires entre le point de départ et la ligne courante).

A ma connaissance, c'est une syntaxe spécifique à Oracle. Si tu veux un truc portable (qui fonctionnerait aussi sous Postgre ou MySQL ou ...), il faut trouver autre chose.

[Jarod46]

Bonjour, oui c'est bien uniquement pour du oracle. J'ai déjà tenter à coups de connect by prior... mais j'avait exactement le même problème. J'ai bien d'autre colonnes à récupérer mais pour l'instant je fait au plus simple. Il y a donc une information parent/enfant dans cette même table, mais il y aussi des tables de correspondants avec colonnes parent/enfant. Il me faut combiner les deux comme conditions. Pour l'instant j'ai bien du mal à comprendre ton exemple, surtout les deux premiers with. Je suis vraiment en débutant en sql et encore plus en oracle même si j'en fait depuis longtemps, je me suis presque toujours contenter de requêtes plus ou moins basiques. En attendant je vais continuer à essayer de comprendre si ton exemple peut m'être utile, merci.

[tbc92]

Les 2 premiers With sont juste là pour 'créer' des pseudo-tables.
Je crée une table aa avec les liens parent/enfant , et une table ab avec le prénom de chaque individu.
Du coup, ma requête est autonome. Tu peux la lancer, elle va marcher directement, alors que je n'ai pas la moindre idée des noms des tables qui existent chez toi.
Ensuite, tu auras simplement à adapter à ton environnement.

En fait , cette requête ci-dessous est valide

Code :

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

1
2
3
4
select qparent, qenfant , level
from aa 
connect by prior qenfant = qparent
start with qparent = 100 ;

à condition bien sûr de mettre les bons noms de tables et de colonnes.

Et comme en général , on veut ajouter des informations issues d'autres tables, on va faire quelque chose de ce genre :

Code :

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

1
2
3
4
5
6
7
8
with ac as (
select qparent, qenfant , level
from aa 
connect by prior qenfant = qparent
start with qparent = 100
), 
select * from ac, xxxx
where xxxx.id = ac.qenfant

Je n'ai pas vérifié, mais de mémoire, on est obligé de procéder de cette façon : faire une première requête récursive, qui liste tous les liens parents/enfants. Et ensuite, lire cette pseudo-table et faire les jointures nécessaires pour rajouter les colonnes de type libellé.

Dans ton cas, tu n'as pas à

[Jarod46]

Ok je pense que je comprend un peu mieux, du coups c'est une solution à ma seconde condition, à savoir de vérifier si une entrée (dans la table S_WO_BILANS dans mon exemple) est un enfant via la table de correspondance (nommée S_WO_BILANS_PROD toujours dans l'exemple) mais il me faut en même temps vérifier si c'est un enfant à l'aide de la colonne ID_WO_PARENT de S_WO_BILANS (dans ton exemple c'est comme si il y avait aussi une colonne qparent dans ta pseudo table ab je pense). C'est vraiment ce qui me corse le tout et que j'ai du mal a expliquer d'ailleurs.

[Jarod46]

Je crois que j'ai finit par trouver à l'aide de chatgpt à force d'essayer de formuler mon problème au mieux, ça donne ç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
21
22
23
24
25
26
 
SELECT 
    ID_WO,
    ID_WO_PARENT,
    LEVEL
FROM (
    SELECT 
        ID_WO, 
        ID_WO_PARENT 
    FROM 
        S_WO_BILANS
    UNION ALL
    SELECT 
        ID_WO_ENFANT AS ID_WO, 
        ID_WO AS ID_WO_PARENT 
    FROM 
        S_WO_BILANS_PROD
    UNION ALL
    SELECT 
        ID_WO_ENFANT AS ID_WO, 
        ID_WO AS ID_WO_PARENT 
    FROM 
        S_WO_BILANS_PROD_REPAIR   
)
START WITH ID_WO = 399852
CONNECT BY PRIOR ID_WO = ID_WO_PARENT;

Il me faut encore vérifier plus en détails avant de valider, mais ça fait propre et j'obtiens bien le même nombres d'enfants avec mon "root" d'exemple que la fonction d'origine en php, en beaucoup beaucoup plus rapide.