Problème sql simple mais compliqué

Version imprimable

21/10/2009, 10h57
kobalt3110

Problème sql simple mais compliqué

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

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 !
21/10/2009, 11h35
kobalt3110

PS : Je suis ouvert à toute restructuration/création de table qui pourrait faciliter la chose... :zoubi:
21/10/2009, 11h38
CinePhil

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.
21/10/2009, 11h50
kobalt3110

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.

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

Code:

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

21/10/2009, 12h43
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

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

Code:

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:

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:

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:

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 +

21/10/2009, 13h02
kobalt3110

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
21/10/2009, 13h05
pacmann

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 ;)
21/10/2009, 13h56
CinePhil

Citation:

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.
21/10/2009, 14h23
kobalt3110

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

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:

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
:ccool:

Merci à tous pour votre aide !:)

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:

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

21/10/2009, 15h37
kobalt3110

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