La structure de base d'un forum n'est pas forcément complexe à réaliser. En revanche créer le système de sujets lus / non lus peut s'avérer être un vrai challenge. En effet, le but est de créer le système le plus léger possible pour la base de donnée et qui nécessite le moins de traitement possibles.
Structure
La structure du forum va être simple :
- Une table categories pour organiser nos différents forums (hasMany Forum)
- une table forums (hasMany Topic, belongsTo Forum)
- une table topics (hasMany Message, belongsTo Forum, belongsTo User)
- une table messages qui contiendra les messages de notre forum (belongsTo Topic, belongsTo User)
- L'incontournable table users pour sauvegarder les utilisateurs.
On va avoir besoin de créer 2 tables pour sauvegarder l'état de lecture des sujets et des forums
- topics_track, sauvegardera la lecture des différents topics du site
- forums_track, permettra le suivi des forums
Dans ces 2 tables on ne sauvegardera pas l'état de lecture, mais plutôt la date de dernière lecture read_at, ce qui nous permettra d'éviter des update trop fréquents.
Pour ne pas nuire à la lisibilité j'ai mis le dump SQL en fin d'article si vous souhaitez reproduire les tables rapidements sur votre installation.
Requêtes
Pour la suite de cet article on partira du principe qu'on est connecté sur le site et que notre id utilisateur est :user_id mais aussi un champs forum_read_at qui permet de connaitre la date de dernière lecture de tous les forums du site.
Consultation du forum :forum_id
Lors de la consultation d'un forum on va chercher à récupérer les différents sujets en faisant la liaison sur les tables de tracking.
SELECT * FROM forums WHERE id = :forum_id;
# Si non connecté
SELECT * FROM topics WHERE forum_id = :forum_id ORDER BY message_at DESC;
# Sinon
SELECT
topics.*,
((topics_track.read_at IS NULL OR topics_track.read_at < message_at) AND (forums_track.read_at IS NULL OR forums_track.read_at < message_at) AND message_at > :forum_read_at) as is_not_read
FROM topics
LEFT JOIN topics_track ON topics_track.topic_id = topics.id AND topics_track.user_id = :user_id
LEFT JOIN forums_track ON forums_track.forum_id = topics.forum_id AND forums_track.user_id = :user_id
WHERE topics.forum_id = :forum_id
LIMIT 1;Consultation du topic :topic_id
C'est lors de cette opération que le gros de l'algorithme aura lieu. En effet, si le topic n'est pas lu il faudra alors mettre à jour la date de dernière lecture pour qu'il soit de nouveau considéré comme lu.
SELECT
topics.*,
((topics_track.read_at IS NULL OR topics_track.read_at < message_at) AND (forums_track.read_at IS NULL OR forums_track.read_at < message_at) AND message_at > :forum_read_at) as is_not_read
FROM topics
LEFT JOIN topics_track ON topics_track.topic_id = topics.id AND topics_track.user_id = :user_id
LEFT JOIN forums_track ON forums_track.forum_id = topics.forum_id AND forums_track.user_id = :user_id
WHERE topics.id = :topic_id
LIMIT 1;
# On récup ici :forum_id qui nous servira pour la suite du sujet
# Si le sujet est non lu : is_not_read = 1
:id = SELECT id FROM topics_track WHERE user_id = :user_id AND topic_id = :topic_id
# Si on trouve un enregistrement {
UPDATE FROM topics_track SET read_at = NOW() WHERE id = :id
# } Sinon {
INSERT INTO topics_track SET read_at = NOW(), user_id = :user_id, topic_id = :topic_id, forum_id = :forum_id
# }
# On vérifie si du coup le forum ne devient pas lu
SELECT COUNT(topics.id) as count
FROM topics
LEFT JOIN topics_track ON topics_track.topic_id = topics.id AND topics_track.user_id = :user_id
LEFT JOIN forums_track ON forums_track.forum_id = topics.forum_id AND forums_track.user_id = :user_id
WHERE
(topics_track.read_at IS NULL OR topics_track.read_at < message_at)
AND (forums_track.read_at IS NULL OR forums_track.read_at < message_at)
AND topics.message_at > :forum_read_at
AND topics.forum_id = :forum_id;
# Si count == 0 (on a tout lu sur le forum)
:id = SELECT id FROM forums_track WHERE user_id = :user_id AND forum_id = :forum_id
# Si on trouve un enregistrement {
UPDATE FROM forums_track SET read_at = NOW() WHERE id = :id
# } Sinon {
INSERT INTO forums_track SET read_at = NOW(), user_id = :user_id, forum_id = :forum_id
# }
# On nettoie la table topics_track
DELETE FROM topics_track WHERE forum_id = :forum_id AND user_id = :user_id
# On vérifie si il reste des forums non lu
SELECT COUNT(forums.id) as count
FROM forums
LEFT JOIN forums_track ON forums_track.forum_id = forums.id AND forums_track.user_id = :user_id
WHERE
(forums_track.read_at IS NULL OR forums_track.read_at < message_at)
AND forums.message_at > :forum_read_at
# Si count == 0 (on a lu tous les forum)
DELETE FROM forums_track WHERE user_id = :user_id;
DELETE FROM topics_track WHERE user_id = :user_id;
UPDATE users SET forum_read = NOW() WHERE id = :user_idCréation de topic
Lorsqu'un topic est créé, il faudra alors mettre à jour la date de dernier message du forum.
INSERT INTO topics SET ....., forum_id = :forum_id
UPDATE forums SET message_at = NOW() WHERE id = :forum_idCréation d'un message
Lors d'un nouveau message, il faudra remonter et mettre à jour le topic mais aussi le forum
INSERT INTO messages SET ......, topic_id = :topic_id;
UPDATE topics SET message_at = NOW() WHERE id = :topic_id;
SELECT forum_id FROM topics WHERE id = :topic_id;
UPDATE forums SET message_at = NOW() WHERE id = :forum_idSuppression d'un topic
La suppresion est un cas un peu chiant car on va chercher à remettre à niveau message_at en fonction du dernier topic.
:forum_id = SELECT forum_id FROM topics WHERE id = :id
DELETE FROM topics WHERE id = :id
:message_at = SELECT message_at FROM topics WHERE forum_id = :forum_id ORDER BY message_at DESC LIMIT 1
UPDATE forums SET message_at = :message_at WHERE id = :forum_idSuppresion d'un message
Même problème que précédemment, on met à jour les message_at du topic parent mais aussi du forum associé.
:topic_id = SELECT topic_id FROM message WHERE id = :id
DELETE FROM messages WHERE id = :id
:created_at = SELECT created_at FROM messages WHERE topic_id = :topic_id LIMIT 1
UPDATE topics SET message_at = :created_at WHERE id = :topic_id;
:forum_id = SELECT forum_id FROM topics WHERE id = :topic_id
:message_at = SELECT message_at FROM topics WHERE forum_id = :forum_id ORDER BY message_at DESC LIMIT 1
UPDATE forums SET message_at = :message_at WHERE id = :forum_idDump SQL
CREATE TABLE IF NOT EXISTS `categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`position` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `forums` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
`category_id` int(11) DEFAULT NULL,
`message_at` datetime DEFAULT CURRENT_TIMESTAMP,
`position` int(11) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `fk_forums_categories_idx` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `forums_track` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL DEFAULT '0',
`forum_id` int(11) NOT NULL DEFAULT '0',
`read_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `forum_id` (`forum_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`topic_id` int(11) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`content` longtext,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_messages_topics1_idx` (`topic_id`),
KEY `fk_messages_users1_idx` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `topics` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`content` longtext,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`forum_id` int(11) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`message_at` datetime DEFAULT NULL,
`sticky` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `fk_topics_forums1_idx` (`forum_id`),
KEY `fk_topics_users1_idx` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `topics_track` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`topic_id` int(11) DEFAULT NULL,
`forum_id` int(11) DEFAULT NULL,
`read_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_topics_track_topics1_idx` (`topic_id`),
KEY `fk_topics_track_users1_idx` (`user_id`),
KEY `forum_id` (`forum_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`forum_read_at` datetime DEFAULT NULL,
`username` varchar(45) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
