Discussion :

[marseillx3]

Bonjour,

Avant toute chose, je tiens à préciser que ma requête actuelle fonctionne, mais je me demande si il n'est pas possible de l'optimiser un peu plus.

Je vous explique, je suis en train de mettre en place un site d'évaluation de compétences interne.

J'ai donc des utilisateurs basiques et des chefs d'équipes. Chaque utilisateur devra remplir un certain nombre d'évaluations de son coté, et leur chef d'équipe devra les évaluer de son coté.

Une fois ces 2 évaluations faites, l’utilisateur et son chef pourront donc se voir afin de comparer et de faire une évaluation finale.

J'ai donc une page ou les chefs d'équipe voient la liste de tout les utilisateurs qu'ils doivent évaluer. Ils doivent aussi voir si l'utilisateur à déjà rempli son évaluation ou si eux même l'ont déjà rempli pour cet utilisateur.

C'est donc pour cette requête que je me questionne.

Tout d'abord, voici les tables utilisées:

• utilisateur (id_utilisateur, nom, prenom, id_type_utilisateur, id_evaluateur)
• theme (id_theme, nom_theme)
• sous_theme (id_sous_theme, id_theme, nom_sous_theme, description) //inutile dans cette requete
• repere (id_repere, id_sous_theme, text_repere) //inutile dans cette requete
• evaluation (id_evaluation, id_theme, id_utilisateur, id_evaluateur, date_evaluation)
• evaluation_value (id_evaluation, id_utilisateur, id_repere, note, commentaire)

Et voici la requête que j'ai actuellement qui fonctionne mais qui ne me parait pas la plus optimisée:

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
 
SELECT
    u.id_utilisateur,
    u.nom,
    u.prenom,
    t.nom_theme,
    e.id_evaluation,
    ev_auto.id_evaluation AS evalAuto,
    ev_tiers.id_evaluation AS evalTiers
FROM
    utilisateur u
INNER JOIN theme t LEFT JOIN evaluation e ON
    e.id_utilisateur = u.id_utilisateur AND e.id_theme = t.id_theme
LEFT JOIN evaluation_value ev_auto ON
    ev_auto.id_evaluation = e.id_evaluation AND ev_auto.id_utilisateur = e.id_utilisateur
LEFT JOIN evaluation_value ev_tiers ON
    ev_tiers.id_evaluation = e.id_evaluation AND ev_tiers.id_utilisateur = e.id_evaluateur
WHERE
    u.id_evaluateur = 1 /*id_evaluateur est variable*/
GROUP BY
    u.id_utilisateur,
    t.nom_theme
ORDER BY
    u.nom ASC

Si evalAuto != NULL, alors l'utilisateur à déjà rempli son évaluation
Si evalTiers!= NULL, alors le chef à déjà rempli l'évaluation de l'utilisateur

N'y a t'il pas une meilleur façon de récupérer ces 2 informations ?

J'espère que mes explications sont suffisamment claires, sinon n'hésitez pas à me demander des précisions.

Merci d'avance pour votre aide !

Bonne journée

[tatayo]

Bonjour,
Avant même de parler d'optimisation, je vois un gros problème dans la requête (même si MySQL ne dit rien): un GROUP BY, mais aucune fonction d'agrégation.
Donc pour les colonnes qui ne sont pas dans le GROUP BY, on ne peut pas savoir quelle valeur sera retournée.
Et vu qu'il n'y a aucune agrégation, à quoi sert donc le GROUP BY ?

Tatayo.

[escartefigue]

Bonsoir,

il manque le critère de jointure pour la table theme
Pour l'optimisation, virez le group by inutile et incohérent (seul MySQL accepte ce genre de bizarrerie)

[marseillx3]

Avant même de parler d'optimisation, je vois un gros problème dans la requête (même si MySQL ne dit rien): un GROUP BY, mais aucune fonction d'agrégation.
Donc pour les colonnes qui ne sont pas dans le GROUP BY, on ne peut pas savoir quelle valeur sera retournée.
Et vu qu'il n'y a aucune agrégation, à quoi sert donc le GROUP BY ?

Imaginons que j'ai 9 thèmes d'évaluations et 3 utilisateurs.
Il faut que je test les 9 thèmes avec les 3 utilisateurs, ce qui fait 27 lignes retourné par la requête, ce qui est correct.
Sans le GROUP BY j'en ai 72 avec mon exemple.

Je suppose donc qu'il y a un autre problème dans la requête mais je n'arrive pas à savoir où.

il manque le critère de jointure pour la table theme
Pour l'optimisation, virez le group by inutile et incohérent (seul MySQL accepte ce genre de bizarrerie)

Pour la table thème, je veux que pour chaque utilisateur, on test chaque thème, il n'y a donc pas de critère de jointure.
De plus, j'ai lu sur un forum que pour un JOIN ou INNER JOIN, la clause ON est optionnelle : https://stackoverflow.com/questions/...ition/16471286

[tatayo]

Si tu veux supprimer les doublons, c'est un DISTINCT qu'il te faut.
S'il ne fait pas l'affaire, c'est que le contenu ses autres colonnes n'est pas le même sur les différentes lignes du résultat.
Et dans ce cas tu ne peux pas savoir quelle valeur va être retournée par MySQL.
D'ailleurs cette requête sera rejetée par n'importe quel SGBD (sauf MySQL).

Tatayo.

[marseillx3]

J'ai modifié ma requête afin de me passer du GROUP BY :

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
SELECT
    DISTINCT 
    u.id_utilisateur,
    u.nom,
    u.prenom,
    t.nom_theme,
    CASE WHEN e.id_evaluation IS NULL THEN 0 ELSE 1 END AS boolEval,
    CASE WHEN ev_auto.id_evaluation IS NULL THEN 0 ELSE 1 END AS boolEvalAuto,
    CASE WHEN ev_tiers.id_evaluation IS NULL THEN 0 ELSE 1 END AS boolEvalTiers
FROM
    utilisateur u
INNER JOIN theme t 
LEFT JOIN evaluation e ON
    e.id_utilisateur = u.id_utilisateur AND e.id_theme = t.id_theme
LEFT JOIN evaluation_value ev_auto ON
    ev_auto.id_evaluation = e.id_evaluation AND ev_auto.id_utilisateur = e.id_utilisateur
LEFT JOIN evaluation_value ev_tiers ON
    ev_tiers.id_evaluation = e.id_evaluation AND ev_tiers.id_utilisateur = e.id_evaluateur
WHERE
    u.id_evaluateur = 1
ORDER BY
    u.nom ASC

Ça parait plus cohérent ou bien y a t-il toujours des erreurs de conception ?

[tatayo]

Comme il n'y a pas de critère de jointure pour la table theme, je remplacerai le INNER JOIN par un CROSS JOIN.
Je doute qu'un autre que MySQL accepterai un INNER JOIN sans le ON associé.
Mais pour MySQL je ne pense pas que ça change grand chose.
Ce serait bien de comparer les deux (plan et temps d'exécution).

Tatayo.

[marseillx3]

Oui c'est vrai, j'avais lu que sans condition de jointure CROSS JOIN était à utiliser.
C'est un oubli de ma part.
En tout cas merci pour tes conseils

[aieeeuuuuu]

bonjour,

j'ai du mal a comprendre votre modèle, et je me demande si le problème ne vient pas de là.

1/ Que représente id_utilsateur dans la table evaluation_value ?
2/ Que représente id_utilsateur dans la table evaluation ?
3/ Que représente id_evaluateur dans la table evaluation ?
4/ Que représente id_evaluateur dans la table utilisateur ?

[marseillx3]

Bonjour,

Tout d'abord, dans la table utilisateur, id_evaluateur est l'évaluateur actuel de l'utilisateur. L'évaluateur est un autre utilisateur.
Dans évaluation il y a id_utilisateur et id_evaluateur, id_utilisateur est en fait la personne évaluée et id_evaluateur est la personne qui évalue.
Et enfin, id_utilisateur dans evaluation_value correspond soit à evaluation.id_utilisateur soit à evaluation.id_evaluateur afin de savoir les notes de l'évaluation correspondent à celle de l'évaluateur ou de l'évalué.

Pour la table évaluation j'ai du remettre id_evaluateur au lieu de faire une jointure entre evaluation et utilisateur car l'évaluateur peut changer ce qui fait que evaluation.id_evaluateur ne correspond pas forcément à utilisateur.id_evaluateur, en cas de changement d'équipe notamment, et il faut garder un historique de toutes les évaluations.

J'ai bien réfléchi mais je ne vois comment organiser la base différemment.

Encore une fois je précise que les requêtes fonctionnent, mais je ne suis pas sûr que ce soit la meilleure façon de faire

[escartefigue]

Bonjour,

Les règles de gestion ne sont pas très claires et le modèle de données encore moins.

D'après ce qui est dit ici

J'ai donc des utilisateurs basiques et des chefs d'équipes. Chaque utilisateur devra remplir un certain nombre d'évaluations de son coté, et leur chef d'équipe devra les évaluer de son coté.
Une fois ces 2 évaluations faites, l’utilisateur et son chef pourront donc se voir afin de comparer et de faire une évaluation finale.

On comprend qu'il y a 3 types d'évaluations :
- les auto-évaluations faites par le collaborateur pour lui même
- les évaluations faites par un hiérarchique du collaborateur (uniquement un chef d'équipe ?)
- les évaluations qui sont la synthèse des deux premières.

Ce qui n'est pas dit mais qu'on suppose :
- les évaluations sont à date, on conserve l'historique
- une évaluation concerne un à plusieurs thèmes

Avec ces règles, on peut en déduire le MCD suivant :
Pièce jointe 519069

Là, j'ai fait en version courte, mais on complètera le MCD ci-dessus avec une asso réflexive entre l'utilisateur et lui même pour la hiérarchie et on modifiera le lien de concerner vers thème à remplacer par sous_thème, lui même en lien avec un et un seul thème

Ce qui donne les tables suivantes :

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
CREATE TABLE UT_UTILISATEUR(
   UT_id INT,
   UT_nom VARCHAR(50) NOT NULL,
   PRIMARY KEY(UT_id)
);
 
CREATE TABLE EV_EVALUATION(
   UT_id INT,
   UT_id_1 INT,
   EV_id INT,
   EV_date DATETIME NOT NULL,
   PRIMARY KEY(UT_id, UT_id_1, EV_id),
   FOREIGN KEY(UT_id) REFERENCES UT_UTILISATEUR(UT_id),
   FOREIGN KEY(UT_id_1) REFERENCES UT_UTILISATEUR(UT_id)
);
 
CREATE TABLE TH_thème(
   th_id INT,
   TH_code CHAR(4) NOT NULL,
   TH_libellé VARCHAR(50) NOT NULL,
   PRIMARY KEY(th_id)
);
 
CREATE TABLE CO_concerner(
   UT_id INT,
   UT_id_1 INT,
   EV_id INT,
   th_id INT,
   CO_note DECIMAL(4,2) NOT NULL,
   PRIMARY KEY(UT_id, UT_id_1, EV_id, th_id),
   FOREIGN KEY(UT_id, UT_id_1, EV_id) REFERENCES EV_EVALUATION(UT_id, UT_id, EV_id),
   FOREIGN KEY(th_id) REFERENCES TH_thème(th_id)
);

[marseillx3]

Oui c'est ça, 3 types d'évaluations, l'auto-évaluation, l'évaluation du supérieur puis une définitive.

En fait il y a 3 niveaux hiérarchiques : Utilisateur basique, Chef d'équipe et Direction.
Les chefs d'équipes peuvent évaluer les utilisateurs basique et la direction peut évaluer les chefs d'équipes.
Mais la structure de la base reste la même.

Les évaluations sont effectivement datées car un utilisateur peut être évaluer plusieurs fois en cas de changement d'équipe.

En revanche, une évaluation ne correspond qu'à un seul par évaluation, donc on a une évaluation par thème, par utilisateur (et par évaluateur si changement d'équipe en cours d'année).

Et c'est important d'avoir id_evaluateur dans la table utilisateur car ça permet d'indiquer qui est son évaluateur à l'instant T.
Donc id_evaluateur dans la table utilisateur n'est pas forcément le même que dans la table evaluation.

J'espère que c'est un peu plus clair à présent.

En tout cas merci beaucoup pour votre aide

[escartefigue]

Et c'est important d'avoir id_evaluateur dans la table utilisateur car ça permet d'indiquer qui est son évaluateur à l'instant T.
Donc id_evaluateur dans la table utilisateur n'est pas forcément le même que dans la table evaluation.

OK, dans ce cas il s'agit, au niveau conceptuel, d'une relation réflexive de l'entité-type utilisateur sur elle même (une occurrence de la relation étant le chef l'autre occurrence le collaborateur)

[SQLpro]

Comme il n'y a pas de critère de jointure pour la table theme, je remplacerai le INNER JOIN par un CROSS JOIN.
Je doute qu'un autre que MySQL accepterai un INNER JOIN sans le ON associé.
Mais pour MySQL je ne pense pas que ça change grand chose.
Ce serait bien de comparer les deux (plan et temps d'exécution).

Tatayo.

NON, NON… Il y a bien une jointure avec t mais elle est dans le prédicat de jointure sous la table "evaluation "

Code :

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

1
2
3
4
5
6
7
...
FROM
    utilisateur u
INNER JOIN theme t 
LEFT JOIN evaluation e ON
    e.id_utilisateur = u.id_utilisateur AND e.id_theme = t.id_theme --> jointure avec t
...

C'est juste horriblement mal codé !

Il faudrait écrire :

Code :

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

1
2
3
4
5
FROM theme t 
    LEFT OUTER JOIN evaluation e
        ON e.id_theme = t.id_theme 
    INNER JOIN utilisateur u
        ON e.id_utilisateur = u.id_utilisateur

Dès lors il saute aux yeux que la jointure externe (OUTER) ne sert à rien !

A +

[tatayo]

Autant pour moi, comme il s'agit d'une jointure interne je n'avais pas regardé les autres jointures.
Les seules fois où je vois ce genre de construction, la requête ressemble à ceci:

Code sql :

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

1
2
3
4
5
6
 
select …
from tableA as a
left outer join tableB as b
inner join tableC as c on b.x = c.x
on b.y = a.y

N'ayant pas vu deux ON d'affilé, j'en avais conclu que le critère de jointure était absent.

Tatayo.

[aieeeuuuuu]

Code :

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

1
2
3
4
5
6
7
...
FROM
    utilisateur u
INNER JOIN theme t 
LEFT JOIN evaluation e ON
    e.id_utilisateur = u.id_utilisateur AND e.id_theme = t.id_theme --> jointure avec t
...

C'est juste horriblement mal codé !

oui

Il faudrait écrire :

Code :

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

1
2
3
4
5
FROM theme t 
    LEFT OUTER JOIN evaluation e
        ON e.id_theme = t.id_theme 
    INNER JOIN utilisateur u
        ON e.id_utilisateur = u.id_utilisateur

Dès lors il saute aux yeux que la jointure externe (OUTER) ne sert à rien !

non, car alors ce n'est sémantiquement plus la même chose, et marseillx3 veut bien un produit cartésien pour avoir chaque theme pour chaque utilisateur.

il faudrait donc plutôt un CROSS JOIN

Code :

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

1
2
3
4
5
6
7
8
...
FROM
    utilisateur u
CROSS JOIN theme t 
LEFT JOIN evaluation e ON
    e.id_utilisateur = u.id_utilisateur 
    AND e.id_theme = t.id_theme 
...

afin que tout soit plus clair

[aieeeuuuuu]

Autant pour moi, comme il s'agit d'une jointure interne je n'avais pas regardé les autres jointures.
Les seules fois où je vois ce genre de construction, la requête ressemble à ceci:

Code sql :

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

1
2
3
4
5
6
 
select …
from tableA as a
left outer join tableB as b
inner join tableC as c on b.x = c.x
on b.y = a.y

N'ayant pas vu deux ON d'affilé, j'en avais conclu que le critère de jointure était absent.

Tatayo.

C'est bien le cas, il s'agit bien d'un produit cartésien.
Pour MySQL (et a ma connaisance seulement lui heureusement), JOIN, INNER JOIN (sans ON) et CROSS JOIN sont équivalents .

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

[SQLpro]

Soit les tables ADHERENT, PRATIQUE (table de jointure) et SPORT

Les requêtes suivantes sont légales :

Code :

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

1
2
3
4
5
6
SELECT *
FROM   dbo.T_ADHERENT_ADR AS A
       JOIN dbo.T_PRATIQUE_PTQ AS P
            ON A.ADR_ID = P.ADR_ID       
       JOIN dbo.T_SPORT_SPT AS S 
            ON P.SPT_ID = S.SPT_ID;

Code :

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

1
2
3
4
5
6
SELECT *
FROM   dbo.T_ADHERENT_ADR AS A
       JOIN dbo.T_PRATIQUE_PTQ AS P
       JOIN dbo.T_SPORT_SPT AS S 
            ON P.SPT_ID = S.SPT_ID
            ON A.ADR_ID = P.ADR_ID

Du fait de la commutativité des opérations de jointure…

La requête suivante n'est pas légale :

Code :

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

1
2
3
4
5
6
SELECT *
FROM   dbo.T_ADHERENT_ADR AS A
       JOIN dbo.T_PRATIQUE_PTQ AS P
       JOIN dbo.T_SPORT_SPT AS S 
            ON A.ADR_ID = P.ADR_ID 
            ON P.SPT_ID = S.SPT_ID

Du fait de la précédence des opérations de jointure…

La requête suivante :

Code :

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

1
2
3
4
5
SELECT *
FROM   dbo.T_ADHERENT_ADR AS A
       JOIN dbo.T_PRATIQUE_PTQ AS P
       JOIN dbo.T_SPORT_SPT AS S 
            ON P.SPT_ID = S.SPT_ID AND A.ADR_ID = P.ADR_ID

N'est pas non plus légale car il faut un ON à chaque JOIN, sauf cas du CROSS JOIN ou du UNION JOIN.

Autrement dit encore une grosse merde de MySQmerde !

A +