Discussion :

[berthos]

Bonjour,

J'ai 3 tables : utilisateur, article (avec un utilisateur_id), commentaire (avec un utilisateur_id et article_id)

Bien entendu, un utilisateur n'a pas forcément un article ou un commentaire.

Si je fais la requête ci-dessous sur les articles, c'est Ok :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
DROP TEMPORARY TABLE IF EXISTS Auteur_TMP; -- Suppression de la table temporaire
CREATE TEMPORARY TABLE Auteur_TMP
ENGINE = InnoDB
SELECT
	utilisateur.pseudo AS Auteur_Pseudo, -- pseudo de l'auteur
	COUNT(article.id) AS Articles_Nb -- Nombre d'articles
FROM utilisateur -- Table des utilisateurs
LEFT JOIN article ON article.auteur_id = utilisateur.id -- Jointure sur les articles
GROUP BY utilisateur.id -- Regroupement par utilisateur
ORDER BY utilisateur.pseudo ASC; -- Tri par pseudo d'utilisateur
 
SELECT * FROM Auteur_TMP;

Si je fais la requête ci-dessous sur les commentaires, c'est Ok :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
DROP TEMPORARY TABLE IF EXISTS Auteur_TMP; -- Suppression de la table temporaire
CREATE TEMPORARY TABLE Auteur_TMP
ENGINE = InnoDB
SELECT
	utilisateur.pseudo AS Auteur_Pseudo, -- pseudo de l'auteur
	COUNT(commentaire.id) AS Commentaires_Nb -- Nombre des commentaires
FROM utilisateur -- Table des utilisateurs
LEFT JOIN commentaire ON commentaire.auteur_id = utilisateur.id -- Jointure sur les commentaires
GROUP BY utilisateur.id -- Regroupement par utilisateur
ORDER BY utilisateur.pseudo ASC; -- Tri par pseudo d'utilisateur
 
SELECT * FROM Auteur_TMP;

Par contre si je mets les 2 LEFT JOIN en même temps, les COUNT sont faux (et identiques entre articles et commentaires) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DROP TEMPORARY TABLE IF EXISTS Auteur_TMP; -- Suppression de la table temporaire
CREATE TEMPORARY TABLE Auteur_TMP
ENGINE = InnoDB
SELECT
	utilisateur.pseudo AS Auteur_Pseudo, -- pseudo de l'auteur
	COUNT(article.id) AS Articles_Nb, -- Nombre d'articles
	COUNT(commentaire.id) AS Commentaires_Nb -- Nombre des commentaires
FROM utilisateur -- Table des utilisateurs
LEFT JOIN article ON article.auteur_id = utilisateur.id -- Jointure sur les articles
LEFT JOIN commentaire ON commentaire.auteur_id = utilisateur.id -- Jointure sur les commentaires
GROUP BY utilisateur.id -- Regroupement par utilisateur
ORDER BY utilisateur.pseudo ASC; -- Tri par pseudo d'utilisateur
 
SELECT * FROM Auteur_TMP;

Voici la structure des tables :

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
-- Création des tables
DROP Table IF EXISTS Article
CREATE TABLE Article (
	id INT UNSIGNED AUTO_INCREMENT,
	titre VARCHAR(200) NOT NULL,
	resume TEXT,
	contenu TEXT NOT NULL,
	auteur_id INT UNSIGNED NOT NULL,
	date_publication DATETIME NOT NULL,
	PRIMARY KEY(id)
);
DROP Table IF EXISTS Utilisateur
CREATE TABLE Utilisateur (
	id INT UNSIGNED AUTO_INCREMENT,
	pseudo VARCHAR(100) NOT NULL,
	email VARCHAR(200) NOT NULL,
	password CHAR(40) NOT NULL,  -- le mot de passe sera hashé avec sha1, ce qui donne toujours une chaîne de 40 caractères
	PRIMARY KEY(id)
);
DROP Table IF EXISTS Commentaire
CREATE TABLE Commentaire (
	id INT UNSIGNED AUTO_INCREMENT,
	article_id INT UNSIGNED NOT NULL,
	auteur_id INT UNSIGNED,
	contenu TEXT NOT NULL,
	date_commentaire DATETIME NOT NULL,
	PRIMARY KEY(id)
);

Est-ce que quelqu'un a une idée ?

Merci d'avance

[berthos]

Solution auto-trouvée : remplacer le COUNT() par un COUNT(DISTINCT)

Ce qui donne :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DROP TEMPORARY TABLE IF EXISTS Auteur_TMP; -- Suppression de la table temporaire
CREATE TEMPORARY TABLE Auteur_TMP
ENGINE = InnoDB
SELECT
	utilisateur.pseudo AS Auteur_Pseudo, -- pseudo de l'auteur
	COUNT(DISTINCT article.id) AS Articles_Nb, -- Nombre d'articles
	COUNT(DISTINCT commentaire.id) AS Commentaires_Nb -- Nombre des commentaires
FROM utilisateur -- Table des utilisateurs
LEFT JOIN article ON article.auteur_id = utilisateur.id -- Jointure sur les articles
LEFT JOIN commentaire ON commentaire.auteur_id = utilisateur.id -- Jointure sur les commentaires
GROUP BY utilisateur.id -- Regroupement par utilisateur
ORDER BY utilisateur.pseudo ASC; -- Tri par pseudo d'utilisateur
 
SELECT * FROM Auteur_TMP;

[Artemus24]

Salut Berthos.

Par contre si je mets les 2 LEFT JOIN en même temps, les COUNT sont faux

C'est normal car en faisant une jointure, vous faites un produit cartésien.

Il y a deux solutions possibles :

1) ne pas faire de count(*). Je rappelle que le count(*) permet de comptabiliser le nombre de lignes.
Vous dites avoir trouvé une solution en faisant un count(distinct), car vous ne comptabilisez pas le nombre de lignes mais le nombres d'articles ou de commentaires distincts.

2) vous pouvez conserver votre count(*), mais quand vous fusionnez vos deux requêtes, faites en sorte de faire en premier le calcul dans une sous-requête, puis ensuite de faire la jointure sur vos deux sous-requêtes.
Si vos résultats sont faux, la cause produit du calcul qui est fait après la jointure et non avant.

@+

[berthos]

Bonjour,

Merci pour votre message. Concrètement quelle serait la requête idéale sachant qu'il faut qu'il y ait un comptage des deux tables ?

[Artemus24]

Salut berthos.

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
select           t1.pseudo,
                 coalesce(t2.nbre_article,     0) as nbre_article,
                 coalesce(t3.nbre_commentaire, 0) as nbre_commentaire
           from  utilisateur as t1
 
left outer join  (  select  auteur_id
                            count(id) as nbre_article
                      from  article
                  group by  auteur_id
                 ) as t2
             on  t2.auteur_id = t1.id
 
left outer join  (  select  auteur_id
                            count(id) as nbre_commentaire
                      from  commentaire
                  group by  auteur_id
                 ) as t3
             on  t3.auteur_id = t1.id
       order by  t1.pseudo asc;

Vous avez trois tables, dont l'une est la table maîtresse (utilisateur).
Dans la première sous-requête (t2), vous faites le comptage de toutes vos lignes.
Dans la seconde sous-requête (t3), vous faites la même chose.

Vous unissez vos deux sous-requêtes en faisant une jointure sur le seul point commun, à savoir la colonne "auteur_id".
Et cette fois-ci, le calcul est fait sans erreur.

@+

[berthos]

Merci beaucoup Artemus24, en effet, c'est une façon de structurer les requêtes que je n'utilise pas assez.
Je vais m'en inspirer pour mes prochaines requêtes.