Bonsoir à tous,

Hum une question me taraude, dans le cadre d'un forum. Comment faire simplement et en un minimum de requête pour récupérer le nom de la catégorie, le nombre de sujets total (dans chaque forum de cette catégorie) et idem pour les messages. Sachant que j'utilise le modèle MCD de base. Une table pour les catégories, une table pour les forums, une table pour les sujets, et une table pour messages avec chacune des tables contenant une clé étrangère pour son référent.

Prenons ce jeu de donnée

idCat     titreCat           sID       mID
--------------------------------------------
2         Catégorie 2         2         3
2         Catégorie 2         2         4
2         Catégorie 2         2         5
2         Catégorie 2         3         6
2         Catégorie 2         4         7

sID est l'id d'un sujet et mID l'id d'un message. Donc on constate qu'il y a 5 messages pour 3 sujets.
J'ai dans l'idée qu'on ne peut pas récupérer en une requête (simple, j'entends par là sans requête dérivée) les 2 informations avec seulement des GROUP BY et COUNT() bien placé. Mais ai-je raison ?

La requête qui sort ce jeu de données est la suivante :

SELECT c.id AS idCat, c.libelle AS titreCat, s.id AS sID, m.id AS mID
FROM categories c
LEFT JOIN forum f ON c.id = id_cat
LEFT JOIN sujets s ON f.id = id_forum
LEFT JOIN messages m ON s.id = id_sujet
WHERE c.id = :id

Merci d'avance pour vos réponses.

Bonsoir,

pour avoir les totaux des COUNT dans le SELECT il te faut ajouter la clause WITH ROLLUP dans le GROUP BY.
Je te laisse te documenter tu comprendras mieux que si j'explique

salut!
le pense que c'est une bonne voie!
regarde ici
peut-etre utile!

Citation : Zazou

Comment faire simplement et en un minimum de requête pour récupérer le nom de la catégorie, le nombre de sujets total (dans chaque forum de cette catégorie) et idem pour les messages

Tu ne peux pas (enfin si avec ROLLUP tu pourrais éventuellement), enfin plutôt tu ne dois pas, puisque ce serait mal de faire des count() sur la table des messages qui contiendra (sur un site qui marche) au minimum plusieurs gigas de posts et/ou plusieurs millions de lignes. Donc, c'est niet, on n'y pense même pas.

Fais comme tous les forums qui existent :

- dans la table des topics, tu stockes le nombre de posts du topic et l'id du dernier post
- dans la table des forums, tu stockes le nombre de topics et l'id du topic qui contient le dernier post
- dans la table des catégories, etc...

Tout ceci mis à jour via des triggers.

Et si on a pas de droit pour faire des trigger, la solution reste viable ?
(Ce qui signifie faire des updates à tout va à chaque fois)

Ou à ce compte-là ne vaudrait-il pas mieux faire des requêtes dérivées pour récupérer ces 2 compteurs ?

Citation : Zazou

PS: Une raison pour que les modérateurs aient été alertés ? Ou c'est juste pour faire chier le monde ?

je me dénonce, j'ai averti les modérateurs. Et non je ne le fais pas "juste pour faire chier"
Ta question n'ayant aucun rapport avec php, je ne vois pas pourquoi tu l'a posée ici.

Et au lieu d'avertir les modérateurs, tu ne crois pas qu'il aurait été plus judicieux de me rediriger vers le forum base de donnée ?
J'avais totalement zappé ce forum, vu qu'il est très récent donc désolé pour l'erreur.

Cela aurait servi à quoi ? tu ne peux pas fermé ton sujet ni le déplacer par toi même. Au mieux tu le met en résolu mais cela ne résous pas le problème

Bref, dans ce cas, il me semble que avertir les modos est le meilleur moyen.

Citation : Zazou

Et si on a pas de droit pour faire des trigger, la solution reste viable ?
(Ce qui signifie faire des updates à tout va à chaque fois)

Ou à ce compte-là ne vaudrait-il pas mieux faire des requêtes dérivées pour récupérer ces 2 compteurs ?</question>

Si tu n'as pas les droits c'est que tu es sur un mutualisé, donc la puissance de BDD disponible est faible, donc une raison de plus pour utiliser des compteurs... Tu peux mettre des UPDATEs dans une transaction, ce sera moins performant qu'un trigger, mais ça va. Dans un forum tu as beaucoup plus de pages vues que d'insertion de posts donc ça vaut le coup d'en tenir compte.

De plus par exemple pour lister les topics dans un forum par ordre du dernier post, tu ferais :

-- 1) tu n'as pas le champ forum_id dans la table posts

SELECT FROM
 (SELECT topic_id, max(post_id) AS last_post_id 
  FROM posts NATURAL JOIN topics WHERE forum_id = $fid 
  ORDER BY last_post_id DESC LIMIT $parpage) lpi
JOIN posts lp ON (lp.post_id=lpi.last_post_id)
JOIN topics t ON (t.topic_id=lpi.topic_id)
JOIN users (2 fois)

-- 2) tu as le champ forum_id dans la table posts

SELECT FROM
 (SELECT topic_id, max(post_id) AS last_post_id 
  FROM posts WHERE forum_id = $fid 
  ORDER BY last_post_id DESC LIMIT $parpage) lpi
JOIN posts lp ON (lp.post_id=lpi.last_post_id)
JOIN topics t ON (t.topic_id=lpi.topic_id)
JOIN users (2 fois)

-- 3) tu as les champs qui vont bien dans la table topic
SELECT FROM topics
JOIN users (2 fois)
WHERE forum_id = $fid
ORDER BY last_post_id DESC LIMIT $parpage

La 1 est catastrophique, la 2 est tolérable si la BDD utilise l'index sur (topic_id,post_id) pour choper le max() (pas évident), la 3 est rapide puisque tu utilises l'index sur (forum_id, last_post_id) pour le tri.

Là où c'est le pire c'est pour la requête des derniers messages non lus d'un utilisateur : si tu n'as pas les champs en cache dans la table topics tu es en pratique obligé de scanner la base pratiquement en entier.

Donc tu préconises la transaction avec tous les update qui vont bien pour mettre à jour les colonnes nb_messages, nb_sujet etc au lieu des requêtes dérivées si je résume ?

Puisque je te tiens, pour la liste des messages non lu, est ce que tu confirmes qu'il faille une table supplémentaire reliant id_cat, id_forum, id_sujet, id_message et id_membre ? (Les 2 premiers sont peut-être facultatifs)

> tu préconises la transaction avec tous les update qui vont bien pour mettre à jour les colonnes nb_messages, nb_sujet etc au lieu des requêtes dérivées si je résume ?

Je te conseille de prendre ta BDD de forum et de la remplir avec un script à la noix qui créé des topics et des posts aléatoirement, genre :
- tu créé une table "serie" avec dedans des ints de 0 à 100000 que tu appelles mettons x

INSERT INTO topics (titre,forum_id) SELECT 'blablabla', xFROM serie WHERE x <= 10001;
INSERT INTO posts (topic_id,post) SELECT 1+pow(rand(),2)*10000, 'quelques centaines de caractères' FROM serie;

Note que tu créé les index APRES (sauf la PK), pas avant, ça ira beaucoup plus vite de créer les index que de les remplir petit à petit.

Essaie d'avoir genre un giga de posts, désactive le query cache, et teste tes requêtes, tu sauras immédiatement si c'est bon ou pas ! Avec une mini BDD, une requête pourrie et une requête bien optimisée tu vois pas le différence, par contre avec des volumes de données réalistes ben c'est 0.1ms (OK) ou une minute (pas top) donc c'est fort instructif.

Que tu fasses tes updates dans un trigger ou dans une transaction, finalement ça revient au même, la seule différence est que le trigger est plus rapide puisqu'il n'y a pas besoin de faire un aller-retour pour chaque requête entre le client et le serveur de BDD. Par conséquent avec un trigger ta transaction est beaucoup plus courte ce qui signifie que les locks qu'elle acquiert dégagent plus tôt, ce qui est bien, mais bon, sur un forum, tu vas jamais insérer 10 posts par seconde, même sur un gros comme le SdZ ça doit être dans les 1 post par minute, donc osef.

Et, oui, il te faut au moins une table qui contient (id_sujet id_membre id_dernier_post_lu) pour marquer que le mec a lu tel post dans tel topic. Après mettre les id_forum et id_cat dedans, pourquoi pas, mais je vois pas trop l'intérêt, après tout on utilise souvent "voir les nouveaux posts dans les sujets où j'ai posté" mais pas très souvent "voir les nouveaux posts dans les sujets où j'ai posté de telle catégorie ou forum". Bon en plus si tu déplaces un topic ou un forum, faudra mettre tout ça à jour !...

En utilisant les colonnes qui vont bien, COUNT() sur les sujets et SUM() sur les messages et on obtient toutes les infos nécessaires sans difficulté.