Requête avec COUNT et jointure [Résolu]

[xtremdisc]

Bonjour à tous,

Je réalise une requête pour un forum pour gérer les messages lus/non-lus.

Pour cette requête j'utilise 3 tables :
FOR_sujets (avec comme champ idSuj, idFor), FOR_messages (avec idMess,idSuj et idFor) et lectures (avec idMembre, idSuj et idMessLastLu)

Voici la requête :

Code :

1
2
3
4
5
6
SELECT S.idSuj, S.titre, COUNT(M.idMess) AS nbMess
FROM FOR_sujets S 
LEFT OUTER JOIN lectures L ON L.idSuj = S.idSuj
LEFT OUTER JOIN FOR_messages M ON L.idSuj = M.idSuj AND M.idMess > L.idMessLastLu
WHERE M.idFor = '".$idFor."' AND L.idMembre = '".$idMembre."'
GROUP BY M.idSuj

La requête fonctionne au niveau des sujets non lus, et me donne bien les idSuj où il y a des messages non-lus ainsi que le nombre de messages non-lu.
Mais ça ne compte pas les nouveaux sujets )où il n'y a pas d'entré avec l'idSuj dans la table lectures)

Pensez vous que ce soit possible d'avoir en même temps les nouveaux sujets en modifiant cette requête (sans en ajouter une autre) ?

Merci d'avance.

[Maljuna Kris]

Saluton,
Je crois qu'en migrant un des filtres dans la jointure externe ça devrait résoudre ton problème.

Code :

1
2
3
4
5
6
SELECT S.idSuj, S.titre, COUNT(M.idMess) AS nbMess
FROM FOR_sujets S 
LEFT OUTER JOIN lectures L ON L.idSuj = S.idSuj
LEFT OUTER JOIN FOR_messages M ON L.idSuj = M.idSuj AND M.idMess > L.idMessLastLu AND L.idMembre = '".$idMembre."'
WHERE S.idFor = '".$idFor."' 
GROUP BY S.idSuj

[xtremdisc]

Merci Maljuna mais ça me renvoit tous les sujets du forum, y compris ceux qui sont lus.

J'ai avancé un peu sur la requête mais je bute encore ...

Code :

1
2
3
4
5
6
SELECT S.idSuj, S.titre, COUNT(M.idMess) AS nbMess 
FROM FOR_sujets S
LEFT OUTER JOIN lectures L ON (L.idSuj = S.idSuj OR S.idSuj NOT IN (SELECT L2.idSuj FROM lectures L2 WHERE L2.idSuj = S.idSuj))
LEFT OUTER JOIN FOR_messages M ON L.idSuj = M.idSuj AND M.idMess > L.idMessLastLu
WHERE S.idFor = '".$idFor."' AND M.idFor = '".$idFor."' AND L.idMembre = '".$idMembre."'
GROUP BY S.idSuj

Ça me renvoit bien les idSuj non lus (y compris ceux non présents dans la table lecture) mais la requête est très longue à renvoyer les résultats
et le nombre de messages comptés sur les idSuj non présent dans lectures est mauvais.

Voici le résultat que je souhaiterais :

idSuj | titre | nbMess
735 |titre 1 | 4
762 |titre 2 | 2
778 |titre 3 | 1 <--- non présent dans lectures
779 |titre 4 | 4 <--- non présent dans lectures

et voici ce que j'obtiens :

idSuj | titre | nbMess
735 |titre 1 | 4
762 |titre 2 | 2
778 |titre 3 | 6 <--- non présent dans lectures (mauvais résultat)
779 |titre 4 | 6 <--- non présent dans lectures (mauvais résultat)

Edit:
Et j'ai remarqué que ce qui ralentit la requête c'est le S.idSuj = '".$idFor."' mais si je l'enlève, ça me retourne en plus les sujets non présents dans lectures présents dans les autres forums ... bizarre que ce soit juste ce filtre qui ralentisse autant !

Edit 2 :
J'ai modifié la place de certains filtres et la requête est rapide maintenant mais je n'obtiens toujours pas le bon COUNT pour les sujets non présents dans lecture (ça donne toujours le même résultat que l'exemple ci-dessus)

Voici le nouvelle requête :

Code :

1
2
3
4
5
6
SELECT S.idSuj, S.titre, S.idFor, COUNT(M.idMess) AS nbMess 
FROM FOR_sujets S 
LEFT OUTER JOIN lectures L ON (L.idSuj = S.idSuj OR S.idSuj NOT IN (SELECT L2.idSuj FROM lectures L2 WHERE L2.idSuj = S.idSuj AND L.idMembre = '".$idMembre."')) AND S.idFor = '".$idFor."' 
LEFT OUTER JOIN FOR_messages M ON L.idSuj = M.idSuj AND M.idMess > L.idMessLastLu AND L.idMembre = '".$idMembre."'	
WHERE M.idFor = '".$idFor."'	
GROUP BY S.idSuj

[Maljuna Kris]

Je ne comprends pas pourquoi tu persistes à filtrer (WHERE) sur une colonne de la table en jointure externe M.idFor et, pire, à grouper sur une colonne de la jointure externe qui peut très bien contenir NULL.
J'avoue que ta logique m'échappe encore assez.

[xtremdisc]

Merci Maljuna de te pencher sur mon cas, c'est vrai que ma première requête était bizarre !

Mais je ne comprends pas le problème avec M.idFor = ... car je souhaite n'avoir que les sujets appartenant à un forum donné. Il faut bien que je mette ce filtre, non ?

J'ai reformulé ma requête, elle sera peut être plus logique ... mais ne donne toujours pas le bon résultat pour les nombres de messages des sujets non présents dans lectures.

Code :

1
2
3
4
5
6
SELECT M.idSuj, S.titre, COUNT(M.idMess) AS nbMess 
FROM FOR_messages M 
LEFT OUTER JOIN FOR_sujets S ON S.idSuj = M.idSuj 
LEFT OUTER JOIN lectures L ON (L.idSuj = M.idSuj AND M.idMess > L.idMessLastLu) OR (M.idSuj NOT IN (SELECT L2.idSuj FROM lectures L2 WHERE L2.idSuj = M.idSuj AND L2.idMembre = '".$idMembre."') AND S.idSuj = M.idSuj) 
WHERE M.idFor = '".$idFor."' AND L.idMembre = '".$idMembre."'
GROUP BY M.idSuj

Et ça me donne :
idSuj | titre | nbMess
735 |titre 1 | 4
762 |titre 2 | 2
778 |titre 3 | 2880 <--- non présent dans lectures (mauvais résultat)
779 |titre 4 | 720 <--- non présent dans lectures (mauvais résultat)

Et je ne sais pas à quoi correspond 2880 et 720 ... Je me suis encore planté quelque part ! Mais là je bute !

[Maljuna Kris]

Je crois avoir compris où se situe le problème.
Il te faut remplacer COUNT(M.idMess) par un comptage conditionnel:

Code SQL :

SUM(IF(L.idSuj IS NULL,0,1)) AS nbMess

Si tu veux rester standard

Code SQl :

SUM(CASE WHEN L.idSuj IS NULL THEN 0 ELSE 1 END ) AS nbMess

[xtremdisc]

Désolé Maljuna mais ça donne le même résultat que COUNT.

Je m'étais résigné à le faire en 2 requêtes séparées, mais en retravaillant dessus pour avoir juste le COUNT des idSuj (pour compter le nombre de nouveaux sujets avant de sélectionner que les 5 premiers avec une LIMIT) et j'ai trouvé la bonne requête qui fonctionne pour le COUNT et le SELECT.

Voici la requête pour le COUNT :

Code :

1
2
3
4
SELECT COUNT(DISTINCT S.idSuj) AS nbSuj FROM FOR_sujets S
LEFT OUTER JOIN lectures L ON L.idSuj = S.idSuj AND L.idMembre = '".$idMembre."'
LEFT OUTER JOIN FOR_messages M ON M.idSuj = S.idSuj
WHERE S.idFor = '".$idFor."' AND (L.idSuj IS NULL OR (M.idSuj = L.idSuj AND M.idMess > L.idMessLastLu))

Et le SELECT final :

Code :

1
2
3
4
5
SELECT S.idSuj, S.titre, COUNT(M.idMess) AS nbMess FROM FOR_sujets S
LEFT OUTER JOIN lectures L ON L.idSuj = S.idSuj AND L.idMembre = '".$idMembre."'
LEFT OUTER JOIN FOR_messages M ON M.idSuj = S.idSuj
WHERE S.idFor = '".$idFor."' AND (L.idSuj IS NULL OR (M.idSuj = L.idSuj AND M.idMess > L.idMessLastLu))
GROUP BY S.idSuj

En tout cas un grand merci à toi Maljuna.
Tes pistes m'ont bien aidé.