Discussion :

[FabaCoeur]

Bonjour,

Dans le cadre du développement d'une application, j'ai un modèle qui comporte une table "ecriture" et une table "complement". Une écriture est une entité qui se caractérise (entre autre) par un complément (je vous laisse consulter le modèle en pièce jointe à ce message avant toute explication superflue).

Ainsi à une écriture, je peux attacher un complément avec plus ou moins de précision.
Par exemple, voici ce que pourrait être une "architecture" de complément:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Notation: id_complement - libelle
 
1-Construction
   2-Bâtiment
      3-Maison
      5-Immeuble
      8-Cabâne
   3-Pont
   6-Route
      7-Chemin
      10-Autoroute
9-Développement
...

Si on suit ce schéma:

• id_parent pour "Construction" et "Développement" vaut NULL
• id_parent pour "Bâtiment" vaut 1
• id_parent pour "Maison" et "Immeuble" vaut 2
• ...

Je peux donc caractériser une écriture par id_complement=2 et je saurai qu'il s'agit d'un Bâtiment (et donc d'une Construction). Je peux rester plus flou et caractériser mon écriture id_complement=1 et là je saurais juste qu'il s'agit d'une Construction (sans avoir plus d'information).

J'ai une requête à construire:
Parmi la liste de toutes mes écritures, je veux retourner les lignes en fonction de leur complément. En clair, j'ai une suite de id_complement et je veux retourner toutes les lignes en faisant partie.

Ainsi si l'utilisateur sélectionne (via des listes) les id_complement suivants:
2 5 10
Il faudrait retourner les lignes qui ont les id_complement valant:
2 3 5 8 10

Comment écrire cette requête ?
Comment organiser les jointures ?
J'ai du mal à écrire cette requête, c'est pourquoi je fais appel à votre aide.

D'avance merci,

Cordialement.

[Chtulus]

Bonsoir,

Une piste ici !

[FabaCoeur]

Bonjour,

J'ai passé en revu le cours (qui cela dit en passant, est très bien réalisé).
Cependant passer par une telle méthode me semble très compliqué, surtout pour ma modeste application dans laquelle je n'aurais pas besoin de faire des requêtes très optimisé puisqu'il n'y aura normalement pas plus de 4 étages pour l'arborescence.

Je compte donc rester sur l'auto jointure.
Cela dit dans le cours la seule et unique méthode qui est donné est l'utilisation de procédure. Je n'en ai jamais faite sous MySQL (et encore moi avec l'appel en PHP...) et je vais essayer de me documenter pour voir si c'est possible.

[SQLpro]

Pour solutionner votre problème il faudra faire une requête contenant 4 fois la table en auto référence à l'aide de jointures externes et utiliser le COALESCE pour empiler les éléments.

Sans le SQL de vos tables, difficile de vous montrer un exemple.

A +

[FabaCoeur]

J'ai regardé la définition du COALESCE et je l'ai comprise, seulement je ne voie pas trop où est ce que vous voulez en venir !

Sinon le code SQL des tables qui correspond au modèle que j'ai donné est celui là:
(Rappel: je travaille sous MySQL)

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
CREATE TABLE complement (
  id_complement INT NOT NULL AUTO_INCREMENT,
  id_parent INT NULL,
  libelle VARCHAR(128) NOT NULL,
  PRIMARY KEY(id_complement),
  INDEX complement_id_complement(id_parent)
);
 
CREATE TABLE ecriture (
  id_ecriture INT NOT NULL AUTO_INCREMENT,
  id_complement INT NOT NULL,
  dateecriture DATE NOT NULL,
  credit FLOAT NOT NULL DEFAULT 0,
  debit FLOAT NOT NULL DEFAULT 0,
  PRIMARY KEY(id_ecriture),
  INDEX ecriture_id_complement(id_complement)
);

Je pensais que c'était clair par le modèle.
Si le schéma que j'ai fait dans mon premier post ne l'est pas non plus, je peux donner des exemples d'insert.

[SQLpro]

Merci d'ajouter à vos tables les clef étrangères sous forme de DRI afin que l'on comprenne ce qui est lié et comment.

A +

[FabaCoeur]

Dans la table "ecriture", id_complement est clé étrangère correspondant à complement.id_complement.
Dans la table "complement", id_parent est clé étrangère correspondant à complement.id_complement.

Cordialement,

[SQLpro]

Voici un exemple de solution :

La table, moins les conneries non SQL de MySQL (INDEX !) :

Code :

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

1
2
3
4
5
6
7
CREATE TABLE complement 
(
  id_complement    INT NOT NULL,
  id_parent        INT NULL FOREIGN KEY REFERENCES complement (id_complement),
  libelle          VARCHAR(128) NOT NULL,
  CONSTRAINT PK_comp PRIMARY KEY(id_complement)
);

Le jeu d'essai :

Code :

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

1
2
3
4
5
6
7
8
9
10
INSERT INTO complement (id_complement, id_parent, libelle) VALUES (1, NULL, 'Construction')
INSERT INTO complement (id_complement, id_parent, libelle) VALUES (2, 1, 'Bâtiment')
INSERT INTO complement (id_complement, id_parent, libelle) VALUES (3, 2, 'Maison')
INSERT INTO complement (id_complement, id_parent, libelle) VALUES (4, 2, 'Immeuble')
INSERT INTO complement (id_complement, id_parent, libelle) VALUES (5, 2, 'Cabâne')
INSERT INTO complement (id_complement, id_parent, libelle) VALUES (6, 1, 'Pont')
INSERT INTO complement (id_complement, id_parent, libelle) VALUES (7, 1, 'Route')
INSERT INTO complement (id_complement, id_parent, libelle) VALUES (8, 7, 'Chemin')
INSERT INTO complement (id_complement, id_parent, libelle) VALUES (9, 7, 'Autoroute')
INSERT INTO complement (id_complement, id_parent, libelle) VALUES (10, NULL, 'Développement')

La requête :

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
SELECT T0.id_complement AS id,
       CASE
          WHEN T3.id_complement IS NOT NULL THEN SPACE(9)
          WHEN T2.id_complement IS NOT NULL THEN SPACE(6)
          WHEN T1.id_complement IS NOT NULL THEN SPACE(3)
          ELSE ''
       END + T0.libelle AS libelle,
       COALESCE(CAST(T3.libelle AS CHAR(128)), '') + COALESCE(CAST(T2.libelle AS CHAR(128)), '')
       + COALESCE(CAST(T1.libelle AS CHAR(128)), '') + COALESCE(CAST(T0.libelle AS CHAR(128)), '') AS Pat
 
FROM   complement AS T0
       LEFT OUTER JOIN complement AS T1
            ON T0.id_parent = T1.id_complement
       LEFT OUTER JOIN complement AS T2
            ON T1.id_parent = T2.id_complement       
       LEFT OUTER JOIN complement AS T3       
            ON T2.id_parent = T3.id_complement       
 
ORDER BY Pat

Je vous conseille de la transformer en vue.

Résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
id          libelle                                                                                                                                   Pat
----------- ----------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           Construction                                                                                                                              Construction                                                                                                                    
2              Bâtiment                                                                                                                               Construction                                                                                                                    Bâtiment                                                                                                                        
5                 Cabâne                                                                                                                              Construction                                                                                                                    Bâtiment                                                                                                                        
4                 Immeuble                                                                                                                            Construction                                                                                                                    Bâtiment                                                                                                                        
3                 Maison                                                                                                                              Construction                                                                                                                    Bâtiment                                                                                                                        
6              Pont                                                                                                                                   Construction                                                                                                                    Pont                                                                                                                            
7              Route                                                                                                                                  Construction                                                                                                                    Route                                                                                                                           
9                 Autoroute                                                                                                                           Construction                                                                                                                    Route                                                                                                                           
8                 Chemin                                                                                                                              Construction                                                                                                                    Route                                                                                                                           
10          Développement                                                                                                                             Développement

A +

[FabaCoeur]

Bonjour,

Merci beaucoup pour ces réponses
Je teste tout ça ce week-end.

Cordialement,

[FabaCoeur]

Bonjour,

Tout fonctionne correctement. Il est vrai que c'est un peu du lourd, surtout si on rajoute des étages, mais je pense qu'en dessous de 3/4 ça reste raisonnable (à condition que la table ne contienne pas des milliers d'enregistrements...).

Merci pour cette solution !

Cordialement,