質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.12%

会員制Webシステムにおける個別メッセージ機能のDB設計

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 3
  • VIEW 1,490

wild

score 29

簡易的な会員制WebシステムをLAMP環境で開発していますが、個別メッセージ機能のDB設計でどうしても解決できない点があり苦慮しています。

長文となりますが、現在の設計と疑問点を記載しますので、アドバイスをいただけませんでしょうか。


【DB設計】

・tbl_board(メッセージボードテーブル)
※やりとりする相手毎に、ユニークな掲示板IDを発行

CREATE TABLE `tbl_board` (
  `id` int(11) NOT NULL COMMENT 'ID',
  `user_name_1` varchar(100) NOT NULL COMMENT 'ユーザー名1',
  `user_name_2` varchar(100) NOT NULL COMMENT 'ユーザー名2',
  `del_flg_1` int(1) NOT NULL DEFAULT '0' COMMENT '削除フラグ(ユーザー1)',
  `del_flg_2` int(1) NOT NULL DEFAULT '0' COMMENT '削除フラグ(ユーザー2)'
) 
ALTER TABLE `tbl_board` ADD UNIQUE( `user_name_1`, `user_name_2`);

INSERT INTO `tbl_board` (`id`, `user_name_1`, `user_name_2`, `del_flg_1`, `del_flg_2`) VALUES
(1, '田中', '山田', 0, 0),
(2, '田中', '高橋', 0, 0),
(3, '山田', '高橋', 0, 0);
(2, 1, 3, 0, 0),
(3, 2, 3, 0, 0);
id user_name_1 user_name_2 del_flg_1 del_flg_2
1 田中 山田 0 0
2 田中 高橋 0 0
3 山田 高橋 0 0

※説明のためにユーザー名を使用していますが、実際はユーザーIDで管理しています。
※「del_flg_1」、「del_flg_2」カラムは、やり取りするどちらか一方がメッセージを削除したい時のために使っています。

・tbl_message(メッセージテーブル)
※メッセージボードIDに紐づけて、送信元やメッセージ本文を保存

CREATE TABLE `tbl_message_pre` (
  `id` int(11) NOT NULL COMMENT 'ID',
  `board_id` int(11) NOT NULL COMMENT 'メッセージボードID',
  `from_user_name` varchar(100) NOT NULL COMMENT '送信ユーザ名',
  `read_flg` int(10) NOT NULL DEFAULT '0' COMMENT '受信者既読フラグ',
  `contents` text NOT NULL COMMENT 'メッセージ本文',
  `created_at` datetime NOT NULL COMMENT '送信日時'
)

INSERT INTO `tbl_message_pre` (`id`, `board_id`, `from_user_name`, `read_flg`, `contents`, `created_at`) VALUES
(1, 1, '田中', 0, '山田さん、はじめまして。田中と言います。', '2017-05-01 00:00:00'),
(2, 1, '山田', 0, '田中さん、連絡ありがとうございます。山田と言います。', '2017-05-02 00:00:00'),
(3, 2, '田中', 0, '高橋さん、はじめまして。田中と言います。', '2017-05-03 00:00:00'),
(4, 3, '田中', 0, '高橋さん、はじめまして。山田と言います。', '2017-05-04 00:00:00'),
id board_id from_user_name read_flg contents created_at
1 1 田中 0 山田さん、はじめまして。田中と言います。 2017-05-01 00:00:00
2 1 山田 0 田中さん、連絡ありがとうございます。山田と言います。 2017-05-02 00:00:00
3 2 田中 0 高橋さん、はじめまして。田中と言います。 2017-05-03 00:00:00
4 3 山田 0 高橋さん、はじめまして。山田と言います。 2017-05-04 00:00:00

【画面とクエリ】

・メッセージ一覧画面
※例:田中さんが、相手別の最新メッセージを一覧表示する場合

SELECT
t1.from_user_name,
t1.read_flg,
t1.contents,
t1.created_at
FROM tbl_board
LEFT JOIN tbl_message t1
ON tbl_board.id = t1.board_id
WHERE EXISTS
(SELECT * FROM tbl_message t2 WHERE t2.board_id = t1.board_id HAVING MAX(t2.created_at) = t1.created_at)
AND
(tbl_board.user_name_1='田中' OR tbl_board.user_name_2='田中')
from_user_name read_flg contents created_at
山田 0 田中さん、連絡ありがとうございます。山田と言います。 2017-05-02 00:00:00
田中 0 高橋さん、はじめまして。田中と言います。 2017-05-03 00:00:00

【疑問点】

1.【画面とクエリ】>「メッセージ一覧画面」で発行したSQLの通り、メッセージ一覧画面用のクエリは出来たのですが、これを最新日付順(created_atカラムを降順)で表示したいと思っています。
単純に、SQLの最後に「ORDER BY t1.created_at DESC」を付加し、以下のSQLを作ったのですが、そうすると検索結果が0件となってしまい、ソート出来ませんでした。

SELECT
t1.from_user_name,
t1.read_flg,
t1.contents,
t1.created_at
FROM tbl_board
LEFT JOIN tbl_message t1
ON tbl_board.id = t1.board_id
WHERE EXISTS
(SELECT * FROM tbl_message t2 WHERE t2.board_id = t1.board_id HAVING MAX(t2.created_at) = t1.created_at)
AND
(tbl_board.user_name_1='田中' OR tbl_board.user_name_2='田中')
ORDER BY t1.created_at DESC


ORDER BYでソートするとなぜ結果が得られないのか、どうしても分かりませんでした。
この原因と解決方法がありましたら、教えていただけませんでしょうか。

2.個別メッセージ機能の開発自体が初めてのため、手探りで設計しており、テーブルの設計がこれで適切なのか不安に感じています。

ご経験者の方で、こういった機能は一般的にどういった作りをする事が多いのか、教えていただければ幸いです。


最終的なクエリ

ご回答くださった方、ありがとうございます。
いただいた情報を元に当方で改めて考え、最終的に以下のクエリに落ち着きました。

yambejp様の回答を元に、一部非相関サブクエリ(IN)を相関サブクエリ(EXISTS)に置き換えてみました。

SELECT * FROM tbl_message as tm1
WHERE tm1.id IN(
    SELECT max(id) FROM tbl_message AS tm2
    WHERE EXISTS (
        SELECT id FROM tbl_board AS tb
        WHERE
        (user_name_1='田中' OR user_name_2='田中')
        AND
        tm2.board_id = tb.id
    )
    GROUP BY board_id
)
ORDER BY created_at DESC
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+2

ちょっと精査してみないとなんともいえませんが
とりあえず動かすだけなら、サブクエリに突っ込んだ上でorder byすればよいでしょう

SELECT * FROM (
SELECT
t1.from_user_name,
t1.read_flg,
t1.contents,
t1.created_at
FROM tbl_board
LEFT JOIN tbl_message t1
ON tbl_board.id = t1.board_id
WHERE EXISTS
(SELECT * FROM tbl_message t2 WHERE t2.board_id = t1.board_id HAVING MAX(t2.created_at) = t1.created_at)
AND
(tbl_board.user_name_1='田中' OR tbl_board.user_name_2='田中')
) AS sub
ORDER BY created_at DESC;

 検証

ざっと検証しました
まず、田中を含むboard_idを取るのはこう

SELECT id FROM `tbl_board` WHERE user_name_1='田中' OR user_name_2='田中';


該当するboard_id毎のmaxのメッセージidをとるのがこう

SELECT board_id,max(id) FROM `tbl_message_pre` 
WHERE board_id IN (SELECT id FROM `tbl_board` WHERE user_name_1='田中' OR user_name_2='田中')
GROUP BY board_id;


※ただし、実際のほしいのはmax(id)だけ

maxのメッセージidから全データを抜き出してソートするのがこう

SELECT * FROM `tbl_message_pre` 
WHERE id in(
SELECT max(id) FROM `tbl_message_pre`
WHERE board_id IN (SELECT id FROM `tbl_board` WHERE user_name_1='田中' OR user_name_2='田中')
GROUP BY board_id
)
ORDER BY created_at DESC;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/05/26 13:17

    ご回答ありがとうございます。
    いただいたSQLで、希望のデータを取得出来ました。
    ただ、もしサブクエリを追加せずに対応出来れば。。と思っており、もし何かヒントがあれば教えていただければ幸いです。

    キャンセル

  • 2017/05/26 13:19

    検証結果を追記しときました

    キャンセル

  • 2017/05/26 14:48

    ご回答ありがとうございます。
    検証の流れを丁寧に書いていただき、順を追って理解する事が出来ました。
    最終的にいただいたクエリをベースに、開発を進めてみようと思います。

    キャンセル

+2

以前、SNSシステムで個別メッセージ機能もあった案件の改修に関わったことがあります。
個人的な意見なのであくまで参考程度にとどめておいてください。

 tbl_boardは不要じゃないかな・・・

田中さんがuser_1で山田さんがuser_2の場合と山田さんがuser_1で田中さんがuser_2の場合で何か違いがあるのであれば別ですが、個別メッセージはあくまで「当人同士のやり取り」で掲示板でいうスレッドが立つのとは意味が違います。
メッセージボードテーブルではひとつひとつのメッセージの削除ではなく、例えば田中さんが山田さんに送ったメッセージ全てを論理削除するということになるのではないでしょうか。

 tbl_messageひとつで管理できそう

メッセージIDをキーとしているのであれば全てメッセージテーブルで管理できそうに思います。
管理すべきは「誰が」「誰に」「どんな内容」を送ったかということです。

message_id from_user to_user message read_flg delete_flg created_at
1 山田 田中 こんにちは田中さん 1 0 2017-05-01 00:00:00
2 田中 山田 メッセージありがとうございます山田さん 0 0 2017-05-02 00:00:00
3 山田 高橋 こんにちは高橋さん 0 0 2017-05-02 00:00:00

メッセージはユーザの画面で見るとして、ログインユーザは「to_user」となります。
集約しても「自分が誰に送信したメッセージか」「自分が誰からメッセージを受け取ったか」というのは取得できると思います。
特定ユーザーとのメッセージやり取りを表示する際には若干コツが必要ですが・・・。

山田さんログインの場合に田中さんとのやり取りをとる場合
※未検証 イメージだけ伝わればと思います

select
 * -- 省略します
from tbl_message_pre
 where (from_user = '山田' or to_user = '山田')
 and (from_user = '田中' or to_user = '田中') 
 order by created_at desc

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/05/26 15:00

    ご回答ありがとうございます。

    記載いただいたような、メッセージテーブル一つで管理する方法については、以下情報を参考に当初設計してみました。

    会員同士のメッセージやりとりのDB設計方法について教えて下さい。
    https://teratail.com/questions/39925

    ただ、やりとりする相手別にグルーピング(from_userまたはto_userが自分)し、その中で最新のレコードを取得する、という方法が分からなかったため断念しました。

    また、「この相手とのやりとりを全て削除したい」となった時、自身のメッセージボックスからだけ削除し、相手のメッセージボックスには残したままにしておきたい、というケースがあると思いました。

    そのため、メッセージテーブルに論理削除フラグを設けるのではなく、メッセージボードテーブルに論理削除フラグを二つ設ける設計としました(del_flg_1、del_flg_2)。

    キャンセル

  • 2017/05/26 15:53

    >ただ、やりとりする相手別にグルーピング(from_userまたはto_userが自分)し、その中で最新のレコードを取得する、という方法が分からなかったため断念しました。

    SQL1発で難しければSQLで「ログイン者とやり取りしている相手の一覧を取得」したあとにプログラム側でループし、SQL「それぞれの相手とのやり取りメッセージの最新を取得」するのもありと思います。
    僕はよくSQL一発だと複雑になりそうな場合はその手でやってます。あまり複雑すぎるSQLはそれはそれで動作に影響があることもありますので。

    >また、「この相手とのやりとりを全て削除したい」となった時、自身のメッセージボックスからだけ削除し、相手のメッセージボックスには残したままにしておきたい、というケースがあると思いました。

    確かにその仕様だとdeleteが2つ必要ですね。んー、確かに2つのテーブルでした方が分かりやすいかな・・・
    既に回答となるようなSQLも出ていることですしね。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.12%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる