簡易的な会員制WebシステムをLAMP環境で開発していますが、個別メッセージ機能のDB設計でどうしても解決できない点があり苦慮しています。
長文となりますが、現在の設計と疑問点を記載しますので、アドバイスをいただけませんでしょうか。
【DB設計】
・tbl_board(メッセージボードテーブル)
※やりとりする相手毎に、ユニークな掲示板IDを発行
sql
1CREATE TABLE `tbl_board` ( 2 `id` int(11) NOT NULL COMMENT 'ID', 3 `user_name_1` varchar(100) NOT NULL COMMENT 'ユーザー名1', 4 `user_name_2` varchar(100) NOT NULL COMMENT 'ユーザー名2', 5 `del_flg_1` int(1) NOT NULL DEFAULT '0' COMMENT '削除フラグ(ユーザー1)', 6 `del_flg_2` int(1) NOT NULL DEFAULT '0' COMMENT '削除フラグ(ユーザー2)' 7) 8ALTER TABLE `tbl_board` ADD UNIQUE( `user_name_1`, `user_name_2`); 9 10INSERT INTO `tbl_board` (`id`, `user_name_1`, `user_name_2`, `del_flg_1`, `del_flg_2`) VALUES 11(1, '田中', '山田', 0, 0), 12(2, '田中', '高橋', 0, 0), 13(3, '山田', '高橋', 0, 0); 14(2, 1, 3, 0, 0), 15(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に紐づけて、送信元やメッセージ本文を保存
sql
1CREATE TABLE `tbl_message_pre` ( 2 `id` int(11) NOT NULL COMMENT 'ID', 3 `board_id` int(11) NOT NULL COMMENT 'メッセージボードID', 4 `from_user_name` varchar(100) NOT NULL COMMENT '送信ユーザ名', 5 `read_flg` int(10) NOT NULL DEFAULT '0' COMMENT '受信者既読フラグ', 6 `contents` text NOT NULL COMMENT 'メッセージ本文', 7 `created_at` datetime NOT NULL COMMENT '送信日時' 8) 9 10INSERT INTO `tbl_message_pre` (`id`, `board_id`, `from_user_name`, `read_flg`, `contents`, `created_at`) VALUES 11(1, 1, '田中', 0, '山田さん、はじめまして。田中と言います。', '2017-05-01 00:00:00'), 12(2, 1, '山田', 0, '田中さん、連絡ありがとうございます。山田と言います。', '2017-05-02 00:00:00'), 13(3, 2, '田中', 0, '高橋さん、はじめまして。田中と言います。', '2017-05-03 00:00:00'), 14(4, 3, '田中', 0, '高橋さん、はじめまして。山田と言います。', '2017-05-04 00:00:00'), 15
|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|
【画面とクエリ】
・メッセージ一覧画面
※例:田中さんが、相手別の最新メッセージを一覧表示する場合
sql
1SELECT 2t1.from_user_name, 3t1.read_flg, 4t1.contents, 5t1.created_at 6FROM tbl_board 7LEFT JOIN tbl_message t1 8ON tbl_board.id = t1.board_id 9WHERE EXISTS 10(SELECT * FROM tbl_message t2 WHERE t2.board_id = t1.board_id HAVING MAX(t2.created_at) = t1.created_at) 11AND 12(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件となってしまい、ソート出来ませんでした。
sql
1SELECT 2t1.from_user_name, 3t1.read_flg, 4t1.contents, 5t1.created_at 6FROM tbl_board 7LEFT JOIN tbl_message t1 8ON tbl_board.id = t1.board_id 9WHERE EXISTS 10(SELECT * FROM tbl_message t2 WHERE t2.board_id = t1.board_id HAVING MAX(t2.created_at) = t1.created_at) 11AND 12(tbl_board.user_name_1='田中' OR tbl_board.user_name_2='田中') 13ORDER BY t1.created_at DESC
ORDER BYでソートするとなぜ結果が得られないのか、どうしても分かりませんでした。
この原因と解決方法がありましたら、教えていただけませんでしょうか。
2.個別メッセージ機能の開発自体が初めてのため、手探りで設計しており、テーブルの設計がこれで適切なのか不安に感じています。
ご経験者の方で、こういった機能は一般的にどういった作りをする事が多いのか、教えていただければ幸いです。
最終的なクエリ
ご回答くださった方、ありがとうございます。
いただいた情報を元に当方で改めて考え、最終的に以下のクエリに落ち着きました。
yambejp様の回答を元に、一部非相関サブクエリ(IN)を相関サブクエリ(EXISTS)に置き換えてみました。
SQL
1SELECT * FROM tbl_message as tm1 2WHERE tm1.id IN( 3 SELECT max(id) FROM tbl_message AS tm2 4 WHERE EXISTS ( 5 SELECT id FROM tbl_board AS tb 6 WHERE 7 (user_name_1='田中' OR user_name_2='田中') 8 AND 9 tm2.board_id = tb.id 10 ) 11 GROUP BY board_id 12) 13ORDER BY created_at DESC 14
回答2件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
退会済みユーザー
2017/05/26 06:00
2017/05/26 06:53