probleme requête

**nicdes** · 13/12/2005, 00h01

Bonsoir...je ne sais pas si je poste au bon endroit...,
J'ai une requête à faire sue ma table mysql et j'ai beau la tourner dans tous les sens, je n'y arrive pas...
en gros, voilà le problème :
je cherche à recuperer les membres qui ont validé toutes les UV d'un diplome...
les champs interessants de mes tables sont les suivants :

table passe :
Id_Membre
Nom_Uv
Valide_UV

table UV :
Nom_UV
Nom_Diplome
et detail important, ma version de mysql est 4.1.9

Merci de votre aide.
a+

**ozzmax** · 13/12/2005, 00h55

Je crois que je peux t'aider mais je ne suis pas certain de ce que tu entend par

je cherche à recuperer les membres qui ont validé toutes les UV d'un diplome...

Précise moi un peu stp

**nicdes** · 13/12/2005, 01h04

Salut,
Dans la table UV, à chaque UV (Nom_Uv) est associé un diplome (Nom_Diplome)...par exemple : l' UV A1 est associée au diplome delf1, A2 à delf, A3 à dalf...etc...
La table passe comporte les UV que chacun des membres ont validées.
Je veux par exemple ramener les membres qui ont validé toutes les UV du delf1 donc qui ont validé les UV A1 et A2 pour cet exemple...
Voilà...je ne sais pas si je suis assez clair...

**nicdes** · 13/12/2005, 01h17

apres relecture, y'a un bleme....pour la cohérence de l'exemple, A1 et A2 sont associés à delf1

**pcaboche** · 13/12/2005, 01h27

Envoyé par nicdes

Bonsoir...je ne sais pas si je poste au bon endroit...

Tu postes bien au bon endroit.

***

Tout d'abord, ton analyse n'est pas complète: on ne répète pas le nom du diplôme pour chaque UV, à la place, on a une table "diplôme" avec un id_diplome, et c'est cet id_diplome que l'on retrouve dans la table uv (clef étrangère)

Ensuite, il y a de forte chances qu'une UV fasse partie de plusieurs diplômes (les UV de "tronc commun"), dans ce cas il faut une relation entre "UV" et "diplome".

Maintenant, admettons qu'une UV fasse partie d'un et un seul diplôme. La table UV ressemble à ça:

table UV :
id_uv
nom_uv
id_diplome

Admettons aussi qu'on ait la table "membre"

On veux la liste des étudiants qui ont validé toutes les UV du diplôme 12. On fait 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
SELECT membre.id_membre
 
FROM uv, membre
 
LEFT OUTER JOIN passe 
ON (uv.id_uv=passe.id_uv AND membre.id_membre = passe.id_membre)
 
WHERE uv.id_diplome=12
 
GROUP BY passe.id_membre
HAVING AVG(IF(passe.valide_uv, 1, 0))=1

Explications:

- D'abord, on fait les combinaisons possibles entre les UV du diplome 12 et les membres. C'est un produit cartésien.

- Ensuite, on regarde si ces combinaisons se retouvent dans la table "passe" grâce au LEFT OUTER JOIN. Si un étudiant n'a pas passé une des UV du diplome 12, les colonnes correspondantes dans la table "passe" seront positionées à NULL.

- si l'étudiant n'a pas validé une UV, valide_uv sera égal à false

- l'expression IF(passe.valide_uv, 1, 0) renverra 0 si passe.valide_uv est égal à false ou NULL. Dans ces conditions, il est impossible que la moyenne (AVG) renvoie 1.

- On ne retourne que les étudiants dont le AVG est égal à 1 (ceux qui ont réussit toutes les UV du diplome): c'est la condition du HAVING

***

Ici, on est obligé de faire un produit cartésien pour trouver les combinaisons entre les membres et les UV du diplôme considéré avant de vérifier celles qui se trouvent dans "passe" (pour savoir si un étudiant s'est bien inscrit dans TOUTES les UV du diplôme).

Bien sûr, au niveau performances, on peut espérer mieux. Aussi, on pourrait essayer de réduire les combinaisons aux seuls membres qui passent au moins une UV du diplôme considéré, mais ça nous oblige à faire une requête imbriquée (ça complique et ce n'est pas évident que l'on gagne en performances à cause de la requête imbriquée). En utilisant une table temporaire, par contre, c'est envisageable.

***

Voilà, tu veux d'autres explications? (notamment dans le cas où une UV serait commune à plusieurs diplômes)

**nicdes** · 13/12/2005, 01h56

merci beaucoup pour ton aide...
Il va falloir que je me penche serieusement la-dessus...
La requête ne fonctionne pas...elle me renvoie aussi les membres ayant validé des UV du diplome et qui ne passent pas celle(s) qui leur manque pour ce diplome (ne renvoient donc pas 0)
Dans le cas que je traite, une UV n'est associée qu'à un seul diplome...ceci dit, je veux bien des explications supplementaires...

**pcaboche** · 13/12/2005, 02h45

Envoyé par nicdes

La requête ne fonctionne pas...elle me renvoie aussi les membres ayant validé des UV du diplome et qui ne passent pas celle(s) qui leur manque pour ce diplome (ne renvoient donc pas 0)

Bon, dans ce cas, il faut y aller pas à pas: enlever le GROUP BY et le HAVING, et voir si ça nous sort le résultat attendu (au niveau des lignes et du résultat de l'expression):

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT *, 
  IF(passe.valide_uv, 1, 0) AS Expr1,
  IFNULL(passe.valide_uv, 0) AS Expr2
 
FROM uv, membre
 
LEFT OUTER JOIN passe
ON (uv.id_uv=passe.id_uv AND membre.id_membre = passe.id_membre)
 
WHERE uv.id_diplome=12

Après on remettra le GROUP BY et on regardera si ça vient du AVG ou du HAVING.

Envoyé par nicdes

Dans le cas que je traite, une UV n'est associée qu'à un seul diplome...ceci dit, je veux bien des explications supplementaires...

Si une UV peut appartenir à plusieurs diplôme, tu auras:
- une table "diplome" (id_diplome, nom_diplome)
- une table "uv" (id_uv, nom_uv)
- une association "uv_appartient_diplome" (id_uv*, id_diplome*)

Il faudra donc modifier la jointure en conséquence:

Code :

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

1
2
3
4
5
6
7
...
FROM uv_appartient_diplome
INNER JOIN uv ON (uv_appartient_diplome.id_uv = uv.id_uv)
...
 
WHERE uv_appartient_diplome.id_diplome=12
...

**nicdes** · 13/12/2005, 09h47

Salut,
Merci pour tes explications tres interessantes....ça me fait bien progresser dans la comprehension des bdd...
La nuit porte conseil....et je me disais qu'en modifiant la table diplome et en lui ajoutant un champ dans lequel on rentrerait le nombre d'UV (Nb_UV) que comporte le diplome on pourrait faire fonctionner la premiere requête en faisant un truc du genre :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT membre.id_membre 
 
FROM uv, membre 
 
LEFT OUTER JOIN passe 
ON (uv.id_uv=passe.id_uv AND membre.id_membre = passe.id_membre) 
 
WHERE uv.id_diplome=12 
 
GROUP BY passe.id_membre 
HAVING SUM(IF(passe.valide_uv, 1, 0))='diplome.Nb_UV'

J'essaierai ce soir en rentrant du boulot....
Qu'en penses-tu ?

**pcaboche** · 13/12/2005, 12h18

Envoyé par nicdes

je me disais qu'en modifiant la table diplome et en lui ajoutant un champ dans lequel on rentrerait le nombre d'UV (Nb_UV) que comporte le diplome on pourrait faire fonctionner la premiere requête en faisant un truc du genre :
(...)
Qu'en penses-tu ?

Bof... Moi j'aime pas trop l'idée: ça t'oblige à rajouter dans ton système une condition d'invariant telle que: "pour tout diplome, le champ nb_uv doit être égal au nombre d'UV dans le diplôme" et donc ça t'oblige à mettre systématiquement à jour ce champs. Dans certains cas ça peut être justifié (quand tu as un traitement long, dont on exploite souvent les résultats), mais là c'est facile de retrouver le nombre d'UV dans le diplôme.

Ta nouvelle requête ne peut pas marcher telle quelle (la table "diplome" n'apparait pas dans le FROM) et elle contient toujours le produit cartésien (qui est couteux en termes de nombre lignes renvoyées). Maintenant, la même chose, sans produit cartésien, en 2 requêtes simples :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT @diplome_nb_uv:=COUNT(*)
FROM uv
WHERE uv.id_diplome=12
GROUP BY uv.id_diplome ;
 
 
SELECT passe.id_membre
FROM passe
INNER JOIN uv ON (uv.id_uv=passe.id_uv)
WHERE uv.id_diplome=12
GROUP BY passe.id_membre
HAVING SUM(IF(passe.valide_uv, 1, 0))=@diplome_nb_uv ;

Ici, on a supprimé le produit cartésien, donc c'est plus performant. Par contre, on est limité à 1 dilpôme particulier, alors qu'avec l'autre solution, on peut faire la recherche sur plusieurs diplômes et même sur tous les diplômes si on veut.

Maintenant, cela n'explique pas pourquoi la requête d'hier ne marchait pas, mais tu regarderas ça ce soir en sortant du boulot.

**nicdes** · 13/12/2005, 21h55

Salut,
Bon voici la mouture actuelle de la requête qui fonctionne mais qui est surement largement optimisable...je ne sais donc pas si on peut considerer le pb comme résolu...

Code :

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

1
2
3
4
5
6
7
8
9
SELECT passe.Id_Membre, membre.Nom, membre.Prenom
  FROM passe,membre
  INNER JOIN uv ON (uv.nom_uv=passe.nom_uv and passe.Id_Membre=membre.Id_Membre)
  WHERE uv.nom_diplome='delf1' and passe.Id_Membre not in (select Id_Membre from valide_diplome where Nom_Diplome='delf1') 
  GROUP BY passe.id_membre
  HAVING SUM(IF(passe.valide_uv, 1, 0))= (SELECT @diplome_nb_uv:=COUNT(*)
  FROM uv
  WHERE uv.nom_diplome='delf1'
  GROUP BY uv.nom_diplome)

J'ai rajouté une contrainte...bien sur, dans la liste des membres ayant toutes les UV, il ne faut pas qu'apparaissent ceux ayant déjà validé le diplome ( table Valide_diplome) puisque l'objet de cette liste est de valider le diplome uniquement des etudiant ne le possedant pas encore...
Chose etonnante...mais en fait pas tant que ça mais ça m'en apprend encore sur le déroulement d'une requête, celle-ci s'eefctue plus rapidement avec cette clause supplémentaire...
Il me reste encore à modifier mes tables pour remplacer mes clés primaires par des Id...
Merci beaucoup pour ton aide !
A+

**pcaboche** · 13/12/2005, 22h39

Oui, on peut encore l'optimiser.

En fait, si ça s'exécute plus vite, c'est parce que tu réduis l'un de tes ensembles de départ (ça joue énormément au niveau d'un produit cartésien!).

***

Tout d'abord, tu n'as pas l'air d'avoir regardé ma requête dans laquelle il n'y a plus de produit cartésien, et donc qui devrait s'exécuter beaucoup plus rapidement (tu veux peut-être bénéficier de sa souplesse, auquel cas, fais-le nous savoir).

Ensuite, pour rester optimal, il vaut mieux éviter les requêtes imbriquées: d'une part il semble qu'elles alongent le temps d'exécution de la requête et d'autre part, elles ne sont pas reconnues en MySQL 4.0 (tu es en 4.1: on n'est pas passé loin !)

Pour supprimer le WHERE NOT IN, il suffit de faire une jointure de type LEFT OUTER JOIN et d'ajouter un test WHERE champ IS NULL (en gras dans le code).

Après quelques modifications:

# On recupère l'id_diplome (évite de faire une autre jointure) et le nombre d'UV

SELECT @id_diplome:=id_diplome, @diplome_nb_uv:=COUNT(*)
FROM uv
WHERE uv.nom_diplome='delf1'
GROUP BY uv.id_diplome ;

# On retourne la liste des nouveau diplômés

SELECT passe.id_membre
FROM passe
INNER JOIN uv ON (uv.id_uv=passe.id_uv)

LEFT OUTER JOIN valide_diplome ON (passe.id_membre=valide_diplome.id_membre)

WHERE uv.id_diplome=@id_diplome
AND valide_diplome.id_membre IS NULL

GROUP BY passe.id_membre
HAVING SUM(IF(passe.valide_uv, 1, 0))=@diplome_nb_uv ;

Sinon, la solution avec le AVG ne marchait vraiment pas? J'avais fait une requête similaire auparavant et ça marchait nickel !

Envoyé par nicdes

Merci beaucoup pour ton aide !

De rien !

**pcaboche** · 13/12/2005, 22h57

Ca y est ! Je crois avoir trouvé ce qui cloche dans ma requête avec le AVG ! Au lieu d'écrire:

Code :

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

GROUP BY membre.id_membre

j'ai malencontreusement écrit:

Code :

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

GROUP BY passe.id_membre

... évidemment, ça change tout! Tout s'explique maintenant !

***

Je reprends donc ma requête avec le AVG (plus souple), en ajoutant ta contrainte concernant les anciens diplomés:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT membre.prenom, membre.nom
 
FROM uv, membre
 
LEFT OUTER JOIN valide_diplome ON (membre.id_membre=valide_diplome.id_membre)
 
LEFT OUTER JOIN passe
ON (uv.id_uv=passe.id_uv AND membre.id_membre = passe.id_membre)
 
WHERE valide_diplome.id_membre IS NULL 
AND uv.nom_diplome='delf1' 
 
GROUP BY membre.id_membre
HAVING AVG(IF(passe.valide_uv, 1, 0))=1

**nicdes** · 14/12/2005, 00h04

Celle-ci fonctionne nickel !
Et elle est effectivement plus simple que celle que j'ai inseré dans mon code....
En fait je n'avais pas utilisé la requête sans produit cartesien car je n'arrivais pas à la faire fonctionner dans mon code php...il me semble que mysql_query ne peut faire qu'une seule operation et pas deux requêtes l'une à la suite de l'autre...mais là encore, je suis un grand débutant...j'ai donc bidouillé tes requêtes pour n'en faire qu'une...bien compliquée je te l'accorde...
Je pense que je vais quand même te "piquer" celle-ci et tu auras droit à un "thanks to" dans mes pages de code !!
Merci beaucoup...je comprends mieux tout ça maintenant !
a+

**pcaboche** · 14/12/2005, 00h09

Envoyé par nicdes

Celle-ci fonctionne nickel !

C'est sûr que ça marche mieux sans les erreurs de copier/coller ! lol

Envoyé par nicdes

il me semble que mysql_query ne peut faire qu'une seule operation et pas deux requêtes l'une à la suite de l'autre...

C'est tout à fait exact (et c'est très logique d'ailleurs...)

Envoyé par nicdes

Je pense que je vais quand même te "piquer" celle-ci et tu auras droit à un "thanks to" dans mes pages de code !!

Ca c'est un immense honneur! lol

**pcaboche** · 14/12/2005, 00h54

Je me demande si on ne pourrait pas encore faciliter le travail de MySQL en changeant l'ordre des jointures.

Au lieu d'écrire:

Code :

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

1
2
3
4
5
FROM uv, membre
 
LEFT OUTER JOIN valide_diplome ON (membre.id_membre=valide_diplome.id_membre)
 
...

On écrirait:

Code :

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

1
2
3
4
5
6
7
FROM membre
 
LEFT OUTER JOIN valide_diplome ON (membre.id_membre=valide_diplome.id_membre)
 
, uv
 
...

Ceci afin de réduire au maximum la taille de l'ensemble membre (puisque qu'une restriction dans le WHERE s'applique sur la table "valide_diplome") avant de faire le produit cartésien. Cela améliorerait les performances.

Peut-être que MySQL fait déjà cette optimisation. A tester...

**nicdes** · 28/12/2005, 21h10

SAlut à toutes et tous,
En fait, le sujet résolu ne l'etait pas tout à fait...
en effet, 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
SELECT membre.prenom, membre.nom 
 
FROM uv, membre 
 
LEFT OUTER JOIN valide_diplome ON (membre.id_membre=valide_diplome.id_membre) 
 
LEFT OUTER JOIN passe 
ON (uv.id_uv=passe.id_uv AND membre.id_membre = passe.id_membre) 
 
WHERE valide_diplome.id_membre IS NULL 
AND uv.nom_diplome='delf1' 
 
GROUP BY membre.id_membre 
HAVING AVG(IF(passe.valide_uv, 1, 0))=1

eliminait les membre possedant déjà un diplome à cause du :

Code :

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

WHERE valide_diplome.id_membre IS NULL

Une petite modif apres deux bonnes heures de reflexion ( et oui....pas tres rapide le gars...) et voilà la requête qui fonctionne jusqu'au prochain bug... :

Code :

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

1
2
3
4
5
6
7
8
9
    $req="SELECT passe.Id_Membre,Prenom, Nom
          FROM uv, membre
          LEFT OUTER JOIN valide_diplome ON (membre.Id_Membre = valide_diplome.Id_Membre)
          LEFT OUTER JOIN passe ON (uv.Id_uv=passe.Id_uv AND membre.Id_Membre=passe.Id_Membre)
          WHERE uv.Id_dip=".$_POST['diplome']."
          AND (valide_diplome.Id_dip IS NULL
              OR valide_diplome.Id_dip <> ".$_POST['diplome'].")
          GROUP BY membre.Id_Membre
          HAVING AVG(IF(passe.valide_uv, 1, 0))=1"

Sur ce, bonnes fêtes !
A+

**pcaboche** · 28/12/2005, 23h47

Envoyé par nicdes

Une petite modif apres deux bonnes heures de reflexion...

Deux heures pour rajouter un :

Code :

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

OR valide_diplome.Id_dip <> ".$_POST['diplome'].")

Si tu pratiques un tarif de consultant, ça fait cher la ligne de code !

(je sais que ce n'est pas le cas...)

Non, en fait je te taquine: faut identifier le problème et trouver une solution, ça peut prendre du temps...

**nicdes** · 29/12/2005, 01h19

Un bon consultant y aurait passé 1/2 heure et aurait facturé la journée !
Malheureusement pour moi, c'est du gratuit...mais ceci dit même si j'y passe du temps, ça m'eclate bien et ça me permet de bien me familiariser avec phpMyAdmin...et puis comme on dit, c'est en.....mouchant qu'on devient moucheron et y'aura quand même une petite récompense au bout ...

A+