Tutoriel Vidéo MySQL Système de sujets lus / non lus

Télécharger la vidéo

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_id

Cré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_id

Cré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_id

Suppression 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_id

Suppresion 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_id

Dump 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;