質問編集履歴

1 最終的なクエリ

wild

wild score 31

2017/05/26 14:51  投稿

会員制Webシステムにおける個別メッセージ機能のDB設計
簡易的な会員制WebシステムをLAMP環境で開発していますが、個別メッセージ機能のDB設計でどうしても解決できない点があり苦慮しています。
長文となりますが、現在の設計と疑問点を記載しますので、アドバイスをいただけませんでしょうか。
---
**【DB設計】**
**・tbl_board(メッセージボードテーブル)**
※やりとりする相手毎に、ユニークな掲示板IDを発行
```sql
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に紐づけて、送信元やメッセージ本文を保存
```sql
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|
---
**【画面とクエリ】**
**・メッセージ一覧画面**
※例:田中さんが、相手別の最新メッセージを一覧表示する場合
```sql
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件となってしまい、ソート出来ませんでした。
```sql
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)に置き換えてみました。
```SQL
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
```
  • MySQL

    9198 questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • データベース設計

    326 questions

    データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

思考するエンジニアのためのQ&Aサイト「teratail」について詳しく知る