Discussion :

[Gryzly]

Bonjour,
Petit nouveau sur le forum et dev depuis quelques mois, je sollicite votre aide pour une requete avec laquelle je bloque depuis plusieurs jours.

Explications du contexte :
2 tables :

#1 : member :
id du salarié
prénom du salarié
nom du salarié
date de naissance
Numéros de sécu
....

#2 piece : qui contient des données sur des pièces et films réalisés :
id de la piece
l'id de l'auteur s'il fait partie des membres de la société
le nom de l'auteur s'il ne fait pas partie de la société
l'id du second auteur s'il fait partie de la société
le nom du second auteur s'il ne fait pas partie de la société
l'id du réalisateur faisant partie de la société
l'id de l'ingénieur du son
l'id de l'ingénieur lumière
....

J'essaye de réaliser la requête suivante :
- Obtenir les pièces d'une des troupes, qui sont en 'Archive' et d'en afficher les prénoms, noms, ... des personnes salariés impliquées dans le projet.
sachant qu'il n'y a que 2 tables et que s'il y a un id(auteur, ingé son, ingé lumière, ...) il s'agit forcément d'un salarié.

Je pense avoir tout essayé avec les INNER JOIN dans touts les sens, les SELECT sur des SELECT, ... Mais ca y est, je suis perdu et serai ravi d'avoir un petit coup de pouce car je ne parviens pas à avoir une requête complète et fiable.

Voici ma dernière requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT *
FROM piece p
INNER JOIN member m1 ON m1.id = p.first_author_id
INNER JOIN member m2 ON m2.id = p.second_author_id
INNER JOIN member m3 ON m3.id = p.director_id
INNER JOIN member m4 ON m4.id = p.sound_director_id
INNER JOIN member m5 ON m5.id = p.light_director_id
INNER JOIN troop t
INNER JOIN piece_troop pt ON t.id = pt.troop_id AND p.id = pt.piece_id
WHERE pt.troop_id LIKE 1 AND p.situation LIKE "Archive"
ORDER BY created_in DESC

Je ne récupère qu'un 1 seul résultat, ce qui n'est pas le résultat attendu.

Merci à ceux qui auront 1, 2 voire 3 idées à me soumettre.

[escartefigue]

Bonjour,

Vos difficultés sont dues à deux causes :

• la première, qui est la plus importante, est que la table 2 est extrèment mal modélisée ! il ne faut jamais avoir un modèle à plat type tableur, c'est contraire aux règles de modélisation des BDD relationelles et vous contraint à faire des requêtes à la fois complexes et très peu performantes
• la deuxième est la mauvaise utilisation des jointures. À cause du modèle à plat (voir point précédent) vous avez toute une collection de colonnes répétées dans la table2 qui bien évidemment sont "nullables" : toute pièce n'a pas forcément un deuxième auteur, un ingénieur du son... Du coup, il faut faire des jointures "OUTER" plutôt qu'"INNER"

Donc la solution de facilité consiste à remplacer vos jointures INNER par des jointures OUTER et la bonne solution consiste à revoir complètement la modélisation de votre BDD

[Gryzly]

Bonjour,
Merci d'avoir pris le temps de jeter un œil à ma question. Je m'attendais à une réponse de ce genre.

J'ai tendance à m'orienter vers votre seconde proposition mais dans ce cadre, si j'ai bien compris, cela signifierai construire d'autres tables (#1 : auteur - membre ou non, #2 : second auteur - membre ou non, #3 : réalisateur - membre ou non, idem ingé son, ingé lumière, ....) ?

Merci pour votre confirmation et bonne journée,

[escartefigue]

Non, a priori, pour ce besoin il faut modéliser une relation ternaire entre pièce, intervenant et rôle :

MCD

INTERVENANT 0,n --- intervenir --- 0,n PIECE
                        │
                        │
               RÔLE 0,n ┘

Ce qui donnerait le MLD suivant
INTERVENANT (IN_id, IN_nom, IN_prenom...)
PIECE(PI_id, PI_nom, PI_synopsis...)
ROLE(RO_id, RO_code, RO_libelle...)
INTERVENIR(IN_id#, PI_id#, RO_id#...)

ainsi dans la table INTERVENIR, vous aurez autant d'occurrences que nécessaire pour enregistrer tous les intervenants d'une pièce en lien avec un ou plusieurs rôles (auteur, co-auteur, éclairagiste, metteur en scène...)

Ceci sous réserve de vos règles de gestion

[Gryzly]

Ok, merci !

Je ne suis pas spécialement au point sur ces aspects là mais je vais chercher dans ce sens.

[Gryzly]

Merci encore.
Je pense avoir compris le concept et la modélisation mais ne perçoit pas comment gérer le fait que certains auteurs par ex) soit externe à l'entreprise car il n'ont pas d'ID. Comment insérer ces externes ? Dans la table contennat les pièces ? Dans la fameuse table ternaire avec l'ensemble des Id des autres tables ?

[escartefigue]

Ce qu'il ne faut surtout pas modéliser, c'est une répétition d'attributs dans une même table du style Table1(Intervenant1, Role1, Intervenant2, Role2, ... Intervenantn, Rolen)
Pour pouvoir bien modéliser, il faut préalablement collecter les règles de gestion avec rigueur ; c'est à dire les écrire, puis les faire valider par les gens du métier, les gestionnaires, pas les informaticiens

[Gryzly]

Ok, donc retour au papier & crayon !
Pour ce qui est de le faire valider par mes contacts, cela va être compliqué : pour eux "faut que ça marche !!!" :-)

[Gryzly]

Ok, bon le papier/crayon n'est pas si mal !

j'ai aboutis à une belle table rejoignant toutes les infos qui se croisaient.

J'ai désormais des tables construites comme suit :

#1 : member (id, first_name, last_name, ....)
#2 : piece (id, piece_name, short_description, created_in, situation, ...)
#3 : skill (id, skill) dans laquelle on retrouve : metteur en scène, ingé son, ingé lumière, ...
#4 : pms (id, piece_id, membre_id, skill_id)

Ma requête précédente fonctionne (avec quelques adaptations) mais je ne parviens pas à me défaire des doublons ( DISTINCT et GROUP BY) n'y font rien !

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
SELECT piece_name, written_by, flyer, short_description, created_in, m.first_name, m.last_name, s.skill
            FROM piece p
            INNER JOIN piece_member_skill pms
                ON p.id = pms.piece_id
            INNER JOIN member m
                ON m.id = pms.member_id
            INNER JOIN skill s
                ON s.id = pms.skill_id
            INNER JOIN troop t
            INNER JOIN piece_troop pt
                ON t.id = pt.troop_id
                AND p.id = pt.piece_id
            WHERE pt.troop_id
            LIKE 1
            AND p.situation
            LIKE "Archive"
            ORDER BY created_in DESC

Si vous aviez un dernier conseil, je suis preneur !:-/

[escartefigue]

Quel est le résultat attendu et le résultat obtenu ? communiquez un extrait.

En plus, dans votre requête, vous mettez en œuvre des tables dont vous n'avez pas communiqué la description (troop, piece_troop), le problème vient certainement des cardinalités n avec ces tables, car si vous ne conservez que les tables communiquées, il devrait y avoir autant de ligne pour chaque pièce qu'il y a d'intervenants dans cette pièce. De toutes façons ces tables ne servent à rien dans votre requête puisque vous n'en utilisez aucune des colonnes.

Quelques remarques :
- si je comprends bien, la table que vous avez nommé "pms" correspond à celle que j'avais nommée "intervenir". D'une part, "pms" me semble beaucoup moins parlant comme nom que "intervenir", mais admettons, mais d'autre part, cette table ne doit pas avoir d'identifiant propre. La PK de PMS est composée des 3 PK héritées des 3 tables concourant à l'association. Rien de plus.
- si vous aviez préfixé ou suffixé les noms de colonne avec un mnémonique de table, comme je l'avais fait dans ma réponse plus haut, la provenance des colonnes serait évidente par exemple PI_id = identifiant primaire de la table PI_pièce et qu'on retrouve comme FK composante de la PK dans "pms"
- il est également beaucoup plus simple de conserver le même nom entre les foreign keys et les primary keys, par exemple dans pms, les FK devraient s'appeler PI_id (identifiant issu de la table PI_piece), RO_id (issu de la table RO_role) et IN_id(issue de IN_intervenant). Comme ça, le nom de la FK suffit à en trouver l'origine et on ne se pose pas de questions métaphysiques pour faire les jointures
- préfixez systématiquement les noms de colonnes par des alias, comme ça on ne se demande pas d'où viennent les colonnes dans la clause select, join, where, group by...
- si votre projet est francophone, débarrassez vous des noms de tables et de colonnes en anglais, c'est une difficulté supplémentaire inutile pour les développeurs qui maitrisent peu la lange de Shakespeare et ça limite les risques de tomber sur les mots réservés SQL qui sont tous des mots anglais. En tout cas, soyez cohérents, pièce c'est du français et skill de l'anglais
- l'opérateur LIKE s'utilise uniquement sur les colonnes de type (var)char, l'utiliser sur la colonne pt.troop_id qui est probablement un integer n'a donc aucun sens
- l'opérateur LIKE s'utilise avec des wildcards underscore ou %, en l'état, LIKE "Archive" ne fonctionnera pas
- les chaînes de caractères se délimitent avec des quotes simples et non pas des apostrophes

[Gryzly]

Bonjour,

Alors, pour répondre dans l'ordre :

Quel est le résultat attendu et le résultat obtenu ?

Le résultat attendu est d'obtenir une liste de pièces répertoriées comme 'Archive' dans 'situation' de la table 'piece' et dont l'id de la troupe n charge du projet est la 1. Le tout classé par date de création par ordre anti-chronologique. Les autres tables contiennent des données à faire apparaître sur la page (distribution de la pièce, auteur, etc...

En plus, dans votre requête, vous mettez en œuvre des tables dont vous n'avez pas communiqué la description (troop, piece_troop), le problème vient certainement des cardinalités n avec ces tables, car si vous ne conservez que les tables communiquées, il devrait y avoir autant de ligne pour chaque pièce qu'il y a d'intervenants dans cette pièce. De toutes façons ces tables ne servent à rien dans votre requête puisque vous n'en utilisez aucune des colonnes.

J'ai bien autant de fois la pièce que j'ai d'intervenant (par ex : 2 auteurs = 2 fois la pièce et 2 auteurs et 1 metteur en scène, 1 ingé lum + 1 ingé son = 5 fois la pièce
L'objectif étant de n'avoir qu'une fois la pièce avec l'ensemble des intervenant

Quelques remarques :

Merci, j'ai bien pris le temps de revérifier l'ensemble des points mentionnés

- si je comprends bien, la table que vous avez nommé "pms" correspond à celle que j'avais nommée "intervenir". D'une part, "pms" me semble beaucoup moins parlant comme nom que "intervenir", mais admettons, mais d'autre part, cette table ne doit pas avoir d'identifiant propre. La PK de PMS est composée des 3 PK héritées des 3 tables concourant à l'association. Rien de plus.

Effectivement j'avais un id propre à chaque entrée : c'est corrigé

- si vous aviez préfixé ou suffixé les noms de colonne avec un mnémonique de table, comme je l'avais fait dans ma réponse plus haut, la provenance des colonnes serait évidente par exemple PI_id = identifiant primaire de la table PI_pièce et qu'on retrouve comme FK composante de la PK dans "pms"
- il est également beaucoup plus simple de conserver le même nom entre les foreign keys et les primary keys, par exemple dans pms, les FK devraient s'appeler PI_id (identifiant issu de la table PI_piece), RO_id (issu de la table RO_role) et IN_id(issue de IN_intervenant). Comme ça, le nom de la FK suffit à en trouver l'origine et on ne se pose pas de questions métaphysiques pour faire les jointures

J'entends et prends bonne note de ces remarques sur les règles de nommage mais je ne peux pas tout modifié maintenant. J'utiliserai ces éléments dans mes projets à venir

- préfixez systématiquement les noms de colonnes par des alias, comme ça on ne se demande pas d'où viennent les colonnes dans la clause select, join, where, group by...
- si votre projet est francophone, débarrassez vous des noms de tables et de colonnes en anglais, c'est une difficulté supplémentaire inutile pour les développeurs qui maitrisent peu la lange de Shakespeare et ça limite les risques de tomber sur les mots réservés SQL qui sont tous des mots anglais. En tout cas, soyez cohérents, pièce c'est du français et skill de l'anglais

Un des objectif de mon travail est de justement accompagner l'internationalisation de l'entreprise donc mentionner les éléments en FR ne m'est pas possible mais comme le point précédent, je promet de faire différemment à l'avenir

- l'opérateur LIKE s'utilise uniquement sur les colonnes de type (var)char, l'utiliser sur la colonne pt.troop_id qui est probablement un integer n'a donc aucun sens
- l'opérateur LIKE s'utilise avec des wildcards underscore ou %, en l'état, LIKE "Archive" ne fonctionnera pas

Ah ? Pour autant, j'ai testé commencé par tester le fonctionnement de ces points avant de rédiger le reste de la requête et le résultat obtenu semble cohérent avec ce que j'ai en BDD

- les chaînes de caractères se délimitent avec des quotes simples et non pas des apostrophes

C'est corrigé, merci mais de la même façon que le point précédent, j'avais pris soin de tester les réactions avant et le résultat obtenu semble cohérent

De nouveau, mes remerciements pour ces éclairages que je garde précieusement.

Je vais essayer de faire en sorte que mon résultat se fasse pour que chaque pièce ne soit mentionnée qu'une fois et que l'ensemble des éléments apparaissent de façon groupés pour chaque pièce.

Bonne journée,

[Gryzly]

Bonjour,

Merci encore pour le coup de main. J'ai pris bonne note des remarques.
Vu l'urgence, je n'ai malheureusement pas le temps de patouiller et de tout refaire.

J'ai pris une autre option en passant par des requêtes indépendantes.

Bonne journée,