Discussion :

[cedrick21]

Bonjour,

je fais appelle à vous car je suis galère depuis 1h sur une requête qui je pense est simple mais bizarrement je bloque dessus.

J'ai trois tables comme suis (simplifiée):
sujets
id
nom

messages
id_sujet
date
id_membre
texte

membres
id
pseudo

Ce que je cherche à faire est la chose suivante :
Lister les différents sujets du forum, indiquer le nombre de message pour chaque sujet et indiquer le dernier membre qui a écrit dans ce sujet.

Pour ce qui est du nom du sujet et du nombre de message pas de soucis

Code :

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

1
2
3
4
5
SELECT s.nom, s.id, COUNT(m.id) as nbMessage
FROM sujets s
LEFT JOIN messages m ON m.id_sujet=s.id
GROUP BY s.id
ORDER BY s.nom ASC

N'arrivant pas à sortir également le dernier membre inscrits j'ai fais une deuxième requête (je suis dans une boucle PHP)

Code :

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

1
2
3
4
SELECT m.pseudo
FROM messages mess
LEFT JOIN membres m ON m.id=mess.id_membre
WHERE mess.id=(SELECT MAX(id) FROM messages WHERE sujet='".$id."')

Le tout fonctionne
mais comme j'ai plusieurs dizaines de sujets ça me fait pas mal de requête
J'aimerais donc tout mixer en 1 requête mais seulement je tourne en rond

Merci de votre aide

[CinePhil]

Reprenons le besoin et décomposons :

Lister les différents sujets du forum, indiquer le nombre de message pour chaque sujet et indiquer le dernier membre qui a écrit dans ce sujet.

1) On va commencer par la fin :

indiquer le dernier membre qui a écrit dans ce sujet

Pour obtenir ceci, il faut connaître la date du dernier message par sujet :

Code :

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

1
2
3
SELECT id_sujet, MAX(date) AS date_max
FROM messages
GROUP BY id_sujet

Au passage, 'date' est un mauvais nom de colonne car c'est un mot réservé du langage SQL.

Ensuite, pour savoir quel membre a écrit ce dernier message, on fait une jointure entre la table et la requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT t1.id_sujet, ms.id_membre
FROM messages ms
INNER JOIN
(
	SELECT id_sujet, MAX(date) AS date_max
	FROM messages
	GROUP BY id_sujet
) t1
	ON t1.id_sujet = ms.id_sujet
	AND t1.date_max = ms.date

2)

indiquer le nombre de message pour chaque sujet

On peut ajouter un simple COUNT à la sous-requête et reprendre son résultat dans la requête principale :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT t1.id_sujet, t1.nb_messages, ms.id_membre
FROM messages ms
INNER JOIN
(
	SELECT id_sujet,
		MAX(date) AS date_max,
		COUNT(*) AS nb_messages
	FROM messages
	GROUP BY id_sujet
) t1
	ON t1.id_sujet = ms.id_sujet
	AND t1.date_max = ms.date

3)

Lister les différents sujets du forum

Un identifiant n'étant pas très parlant, il suffit de faire une jointure des deux autres tables avec 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
15
16
17
18
19
20
SELECT s.nom AS sujet,
	COALESCE(t2.nb_messages, 0) AS nb_messages,
	COALESCE(m.nom, 'Néant') AS dernier_posteur
FROM sujets s
LEFT OUTER JOIN
(
	SELECT t1.id_sujet, t1.nb_messages, ms.id_membre
	FROM messages ms
	INNER JOIN
	(
		SELECT id_sujet,
			MAX(date) AS date_max,
			COUNT(*) AS nb_messages
		FROM messages
		GROUP BY id_sujet
	) t1
		ON t1.id_sujet = ms.id_sujet
		AND t1.date_max = ms.date
) t2 ON t2.id_sujet = s.id
	LEFT OUTER JOIN membres m ON m.id = t2.id_membre

COALESCE donne la première valeur non nulle parmi ce qui figure dans ses parenthèses. C'est pour le cas où un sujet n'aurait pas de message.