WebDesign Dackel

MySQL – お知らせなどの既読管理についてメモ

MySQL - お知らせなどの既読管理についてメモ

Hatena0
Google+0
Pocket0
Feedly0

DBMySQLを使用したサービス内でお知らせをする時に、各ユーザ毎に、既読・未読の管理をしたかったのでその時のメモです。

ここで言う管理とは、例えば既読にマークを付けたり、未読の一覧を実現したりする部分としておきます。

前提条件や実装方法など

SQL文を書く前に前提条件の整理です。

要件

  • お知らせはパブリックな扱いで、全ユーザに対して通知するもの
  • CookielocalStorageだと実装は簡単。しかし…
    • 例えば、スマホでログインしてお知らせを読む。PCの同じアカウントでログインすると未読扱いになる
    • その為、既読履歴の永続化は必須とする
  • データ量は最低限としたい

実装方法

色々な実装方法が考えられますが、今回は次の方法で実装します。

  • 既読の判定
    • ユーザがお知らせを読んだ際にお知らせとの関連付けを行う
    • 関連付けの行われているお知らせは既読とする
  • 未読の判定
    • 上記の関連付けが行われていないお知らせは未読とする

これだけだとイメージが湧きづらいので、以下では簡略化したサンプルを用意して動作を確認していきます。

テーブル構成

notiy_sampleというテーブルがあるとして、テーブル構成は以下とします。

テーブル本体

mysql> SHOW TABLES;
+-------------------------+
| Tables_in_notify_sample |
+-------------------------+
| notices                 |
| notices_users           |
| users                   |
+-------------------------+
3 rows in set (0.00 sec)

usersテーブル

ユーザの情報を保持するためのテーブルです。

mysql> DESC `users`;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(64)      | NO   |     | NULL    |                |
| email | varchar(128)     | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


# テーブルの中身
mysql> SELECT * FROM `users`;
+----+--------+--------------------+
| id | name   | email              |
+----+--------+--------------------+
|  1 | 鈴木 | suzuki@example.com |
|  2 | 佐藤 | satou@example.com  |
|  3 | 和田 | wada@example.com   |
+----+--------+--------------------+
3 rows in set (0.00 sec)

noticesテーブル

お知らせの本体情報を格納するテーブルです。お知らせのタイトルと内容を保持します。

mysql> DESC `notices`;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title    | varchar(128)     | NO   |     | NULL    |                |
| contents | text             | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


# テーブルの中身
mysql> SELECT * FROM `notices`;
+----+---------------+------------------------+
| id | title         | contents               |
+----+---------------+------------------------+
|  1 | お知らせ1 | お知らせ1の内容 |
|  2 | お知らせ2 | お知らせ2の内容 |
|  3 | お知らせ3 | お知らせ3の内容 |
+----+---------------+------------------------+
3 rows in set (0.00 sec)

notices_usersテーブル

ユーザ情報とお知らせの関連付けを保持しておくためのテーブルとします。

mysql> DESC `notices_users`;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| notice_id | int(10) unsigned | NO   |     | NULL    |                |
| user_id   | int(10) unsigned | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)


# データの中身
mysql> SELECT * FROM `notices_users`;
+----+-----------+---------+
| id | notice_id | user_id |
+----+-----------+---------+
|  1 |         1 |       2 |
|  2 |         2 |       3 |
+----+-----------+---------+
2 rows in set (0.00 sec)

実際はもっと複雑なテーブルになると思いますが、サンプルなので極力シンプルな内容としています。

既読・未読の一覧を取得してみる

上記テーブル構成で、未読・既読それぞれを取得してみたいと思います。

既読のお知らせ一覧

既読ではテーブルを結合して、関連付いたお知らせを取得するだけなので簡単です。

以下のSQLで、佐藤さん(id=2のユーザ)が読んだお知らせの一覧を取得できます。

SELECT Notice.* FROM `notices` AS Notice
INNER JOIN `notices_users` AS Rel ON Notice.id = Rel.notice_id
INNER JOIN `users` AS Users ON Users.id = Rel.user_id
WHERE Users.id = 2;

AS句は好みで付けているので必須ではありません。適宜読み替えていただけたらと思います。

実際に実行した結果は以下。

+----+---------------+------------------------+
| id | title         | contents               |
+----+---------------+------------------------+
|  1 | お知らせ1 | お知らせ1の内容 |
+----+---------------+------------------------+
1 row in set (0.00 sec)

未読のお知らせ一覧

SELECT句でサブクエリを使うことで、関連していない状態を表現できます。

以下のSQLで、和田さん(id=3のユーザ)がまだ読んでいないお知らせの一覧を取得できます。

SELECT Notice.* FROM `notices` AS Notice
WHERE NOT EXISTS (
  SELECT * FROM `notices_users` AS Rel
  WHERE Rel.notice_id = Notice.id
  AND Rel.user_id = 3
);

実際に実行した結果は以下。

+----+---------------+------------------------+
| id | title         | contents               |
+----+---------------+------------------------+
|  1 | お知らせ1 | お知らせ1の内容 |
|  3 | お知らせ3 | お知らせ3の内容 |
+----+---------------+------------------------+
2 rows in set (0.00 sec)

これで、既読・未読とも無事取得することができました。 もし「こっちの方法の方が良い!」というものありましたら、ご教授いただけますと嬉しいです…!