Problème sql simple mais compliqué

**kobalt3110** · 21/10/2009, 10h57

Bonjour à tous !!!
(premier message sur ce forum, grande émotion

)

Je suis actuellement en train de développer un espace d'échange de fichiers
entre utilisateurs en php, le principe est très bateau, mais ca reste un bon exercice.
Comment ça marche (ou du moins comment c'est sensé marché)? Chaque utilisateur peut diffuser des fichiers sur son espace perso à un ou plusieurs autres membres.
J'ai actuellement 3 tables SQL:

- utilisateurs: id_utilisateur, nom_utilisateur, login, pass, email
- fichiers : id_fichier, nom fichier
- droits_de_regard : id_fichier, id_expediteur, id_destinataire

Schéma donc très simple, avec la table droits_de_regard qui est la seule un peu tordue...

Exemple : utilisateur1 dépose un fichier nommé "exemple.pdf" pour utilisateur2 et utilisateur3. Ce fichier aura l'id numéro 1
La table droits_de_regard se complète ainsi :

id_fichier || id_expediteur || id_destinataire
1 || id_utilisateur1 || id_utilisateur2
1 || id_utilisateur1 || id_utilisateur3
Donc s'il y a X destinataires, X enregistrements seront créés.

Mon application comprend un espace d'administration avec une partie d'administration des fichiers présents sur le serveur. Dans cette partie je veux lister les fichiers de la façon suivante:

Nom fichier || Nom expediteur || Nom destinataire(s)
exemple.pdf || utilisateur1 || utilisateur2, utilisateur3

QUESTION: Quelle requête SQL pourrait me générer ce résultat?
(Je ne sais pas comment faire correspondre les id et les noms, ni regrouper les destinataires en une seule ligne)...

Je ne sais pas si j'ai été très clair, si des choses vous échappent n'hésitez pas à me demander !

D'avance, merci de votre aide !

**kobalt3110** · 21/10/2009, 11h35

PS : Je suis ouvert à toute restructuration/création de table qui pourrait faciliter la chose...

**CinePhil** · 21/10/2009, 11h38

Ce que tu cherches à faire est de la cosmétique et n'est pas le boulot du SGBD mais du programme utilisateur donc du langage de ce programme.

Néanmoins, si ton SGBD est MySQL, tu peux regarder du côté de GROUP_CONCAT.

**kobalt3110** · 21/10/2009, 11h50

Alors mettons que je veuille présenter mes infos ainsi:

Nom fichier || Nom expediteur || Nom destinataire(s)
exemple.pdf || utilisateur1 || utilisateur2
exemple.pdf || utilisateur1 || utilisateur3

Le problème restant est alors de faire correspondre les id avec les différents noms dans les différentes tables.

**CinePhil** · 21/10/2009, 12h09

Pas très compliqué ! Quelques jointures pour retrouver les infos :

Code :

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

1
2
3
4
5
6
SELECT f.nom_fichier, u1.nom_utilisateur AS Nom_Expéditeur, u2.nom_utilisateur AS Nom_Destinataire
FROM droits_de_regard AS d
INNER JOIN utilisateurs AS u1 ON d.id_expediteur = u1.id_utilisateur
INNER JOIN utilisateurs AS u2 ON d.id_destinataire = u2.id_utilisateur
INNER JOIN fichiers AS f ON d.id_fichier = f.id_fichier
ORDER BY f.nom_fichier, u1.nom_utilisateur, u2.nom_utilisateur

**kobalt3110** · 21/10/2009, 12h43

Merci beaucoup pour la réponse,
mais la requête donnée ma donne l'erreur suivante :
<< Unknown column 'f.nom_fichier' in 'field list' >>

( erreur envoyée par un or die(mysql_error()); )

N'étant pas pro, je n'arrive pas bien à comprendre d'ou viens cette erreur

**SQLpro** · 21/10/2009, 12h45

Vos tables d'origine sont parfaites.... Voici comment obtenir vos info :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE utilisateurs 
(id_utilisateur int PRIMARY KEY, 
 nom_utilisateur varchar(8)) --, login , pass, email
 
CREATE TABLE fichiers 
(id_fichier int PRIMARY KEY, 
 nom_fichier  varchar(8))
 
CREATE TABLE droits_de_regard  
(id_fichier int FOREIGN KEY REFERENCES fichiers (id_fichier),
 id_expediteur int FOREIGN KEY REFERENCES utilisateurs (id_utilisateur), 
 id_destinataire  int FOREIGN KEY REFERENCES utilisateurs (id_utilisateur),
 cONSTRAINT PK PRIMARY KEY (id_fichier, id_expediteur, id_destinataire))

Jeu d'essais :

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
INSERT INTO utilisateurs VALUES (1, 'toto')
INSERT INTO utilisateurs VALUES (2, 'titi')
INSERT INTO utilisateurs VALUES (3, 'tata')
INSERT INTO utilisateurs VALUES (4, 'tutu')
INSERT INTO utilisateurs VALUES (5, 'tete')
INSERT INTO utilisateurs VALUES (6, 'tyty')
 
INSERT INTO fichiers VALUES (11, 'File11')
INSERT INTO fichiers VALUES (12, 'File12')
INSERT INTO fichiers VALUES (13, 'File13')
INSERT INTO fichiers VALUES (14, 'File14')
INSERT INTO fichiers VALUES (15, 'File15')
 
INSERT INTO droits_de_regard VALUES (11, 1, 2)
INSERT INTO droits_de_regard VALUES (11, 1, 3)
INSERT INTO droits_de_regard VALUES (11, 1, 5)
INSERT INTO droits_de_regard VALUES (12, 3, 1)
INSERT INTO droits_de_regard VALUES (13, 2, 3)
INSERT INTO droits_de_regard VALUES (13, 2, 5)
INSERT INTO droits_de_regard VALUES (14, 5, 1)

Solution en 1 requête SQL norme SQL:1999 :

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
WITH
T0 AS
(SELECT id_fichier, id_expediteur, id_destinataire, 
        ROW_NUMBER() OVER(PARTITION BY id_fichier, id_expediteur 
                              ORDER BY id_destinataire) AS N,
        COUNT(*) OVER(PARTITION BY id_fichier, id_expediteur) AS NN
 FROM   droits_de_regard),
T1 AS
(SELECT id_fichier, id_expediteur, 
        CAST(id_destinataire AS VARCHAR(max)) as ids_tous_destinataires, 
        CAST(nom_utilisateur  AS VARCHAR(max)) as tous_destinataires, N, NN
 FROM   T0
        INNER JOIN utilisateurs AS U
              ON T0.id_destinataire = U.id_utilisateur
 WHERE  N = 1
 UNION  ALL
 SELECT T1.id_fichier, T1.id_expediteur,  
        T1.ids_tous_destinataires + ', ' + CAST(tu.id_utilisateur AS VARCHAR(max)),
        T1.tous_destinataires + ', ' + tu.nom_utilisateur, T0.N, T0.NN
 FROM   T1
        INNER JOIN T0
              ON T1.id_fichier = T0.id_fichier
              AND T1.id_expediteur = T0.id_expediteur
              AND T1.N + 1 = T0.N
        INNER JOIN utilisateurs AS tu
              ON T0.id_expediteur = tu.id_utilisateur)
SELECT T1.id_fichier, T1.id_expediteur, u.nom_utilisateur AS nom_expéditeur, 
       T1.ids_tous_destinataires, T1.tous_destinataires
FROM   T1
       INNER JOIN utilisateurs AS u
             ON T1.id_expediteur = u.id_utilisateur
WHERE  N = NN

Résultat :

Code :

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

1
2
3
4
5
6
id_fichier  id_expediteur nom_expéditeur ids_tous_destinataires   tous_destinataires
----------- ------------- -------------- ------------------------ -------------------
12          3             tata           1                        toto
14          5             tete           1                        toto
13          2             titi           3, 2                     tata, titi
11          1             toto           2, 1, 1                  titi, toto, toto

Sur les CTE et la récursivité : http://sqlpro.developpez.com/cours/s...te-recursives/

A +

**kobalt3110** · 21/10/2009, 13h02

Heu...

merci beaucoup, SQLpro, jamais j'aurai pu pondre une syntaxe pareille,
le IC, c'est qu'il semble y avoir une erreur de syntaxe...

Retour de $res = mysql_query($sql) or die(mysql_error());
($sql étant la requette que tu m'as donné)

<< You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITH T0 AS (SELECT id_fichier, id_expediteur, id_destinataire, ' at line 1 >>

Version MySql : 5.1.36 sur WAMPSERVEUR

**pacmann** · 21/10/2009, 13h05

Salut !

Si tu avais lu en entier la réponse de Cinephile, tu aurais vu la solution :
GROUP_CONCAT

C'est très simple d'utilisation, donc je te laisse te documenter comme un grand

**CinePhil** · 21/10/2009, 13h56

Envoyé par kobalt3110

Merci beaucoup pour la réponse,
mais la requête donnée ma donne l'erreur suivante :
<< Unknown column 'f.nom_fichier' in 'field list' >>

( erreur envoyée par un or die(mysql_error()); )

N'étant pas pro, je n'arrive pas bien à comprendre d'ou viens cette erreur

Ceci veut dire que la colonne nom_fichier de la table f n'exite pas. Comme f est l'alias de la table fichiers, à toi de trouver où est l'erreur de nom (table fichiers ou colonne nom_fichier) et de corriger la requête.

Quant à la solution de SQLPro, comme tu utilises MySQL, je crains qu'elle ne puisse pas fonctionner.

**kobalt3110** · 21/10/2009, 14h23

Merci pour toutes vos réponses c'est cool,
je continue à creuser, je fermerai le post si je m'en sort !

**kobalt3110** · 21/10/2009, 14h59

Autant pour moi Cinephile, la solution se trouvait bien du côté de GROUP_CONCAT, je m'étais pas encore intéressé à cette solution.

Au final :

Code :

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

1
2
3
SELECT f.nom_fic FICHIER, u1.nom EXPEDITEUR, GROUP_CONCAT(u2.nom ORDER BY u2.nom DESC SEPARATOR ", ") DESTINATAIRE
FROM fichiers f, utilisateurs u1, utilisateurs u2, droits_de_regard
WHERE f.id = droits_de_regard.id_fichier and u1.id = droits_de_regard.id_expediteur and u2.id = droits_de_regard.id_destinataire

Merci à tous pour votre aide !

**CinePhil** · 21/10/2009, 15h17

Beurk !
Dans ta requête, il manque le GROUP BY puisque GROUP_CONCAT est une fonction de regroupement.
Avec les jointures normalisées depuis 1992 et un alias de table pour éviter de répéter droits_de_regard et faciliter la lecture de la requête, c'est mieux :

Code :

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

1
2
3
4
5
6
7
SELECT f.nom_fic FICHIER, u1.nom EXPEDITEUR, 
  GROUP_CONCAT(u2.nom ORDER BY u2.nom DESC SEPARATOR ", ") DESTINATAIRE
FROM fichiers f
INNER JOIN droits_de_regard d ON f.id = d.id_fichier 
  INNER JOIN utilisateurs u1 ON u1.id = d.id_expediteur
  INNER JOIN utilisateurs u2 ON u2.id = d.id_destinataire
GROUP BY f.nom_fic

**kobalt3110** · 21/10/2009, 15h37

Thx pour l'optimisation !
Il est vrai que je l'avais codée un peu à l'ancienne lol

Problème sql simple mais compliqué

Requêtes MySQL

Discussions similaires

Partager

Partager