3つのテーブルを結合して最新メッセージを取得するSQLがわかりません

Question

MySQLで3つのテーブルを結合して、
１．ユーザーと友達登録している人の一覧を出す
２．各々の登録情報を出す
３．最新メッセージを取得する
というSQL文を書こうとしていますが、複雑すぎてどうすればいいのかわかりません。

A)userテーブル（登録ユーザーの情報

create table users(
no int AUTO_INCREMENT NOT NULL PRIMARY KEY,
id text,
name text(20),
icon text(255),
password text(30)
);

INSERT INTO users (id, name, icon, password) VALUES
('aaa', '田中', 'icon.jpg', 'xxx'),
('bbb', '佐藤', 'icon.jpg', 'xxx'),
('ccc', '斎藤', 'icon.jpg', 'xxx'),
('ddd', '山田', 'icon.jpg', 'xxx')

B) f_relationテーブル（友達関係）

create table f_relation(
id int AUTO_INCREMENT NOT NULL PRIMARY KEY,
user_id text,
friend_id text(20)
)

INSERT INTO f_relation (user_id, friend_id) VALUES
('aaa', 'bbb'),
('bbb', 'aaa'),
('aaa', 'ccc'),
('ccc', 'aaa'),
('aaa', 'ddd'),
('ddd', 'aaa'),

C) talkテーブル（会話内容）

create table talk(
id int AUTO_INCREMENT NOT NULL PRIMARY KEY,
message text,
sender text,
receiver text,
timestamp timestamp,
read_flg int
)

INSERT INTO talk (message, sender, receiver, timestamp, read_flg) VALUES
('古いメッセージ', 'bbb', 'aaa', '2019-12-05 10:35:21', 1),
('新しいメッセージ', 'bbb', 'aaa', '2019-12-06 10:35:21', 1),
('古いメッセージ', 'ccc', 'aaa', '2019-12-05 11:30:21', 0),
('新しいメッセージ', 'aaa', 'ccc', '2019-12-06 9:35:21', 1),
('古いメッセージ', 'ddd', 'aaa', '2019-12-04 8:35:21', 1),
('新しいメッセージ', 'ddd', 'aaa', '2019-12-05 10:30:20', 0)

現在は以下のSQLによって下の画像の結果を得ています。

SELECT u.*, t.message, MAX(timestamp), COUNT(read_flg = 0 OR NULL) AS unread FROM talk AS t
RIGHT JOIN f_relation AS f ON t.receiver = f.user_id && t.sender=f.friend_id
RIGHT JOIN users AS u
ON f.friend_id = u.id where f.user_id = 'aaa'
group by f.friend_id
order by MAX(timestamp) DESC

ここでは仮に、
user_id = 「aaa」さんと友達の一覧を表示し、各々の登録情報と、最新メッセージを取得、メッセージの新しい順に並べ替えようとしています。
友達とのやり取りで、自分が送信したメッセージと、自分が受信したメッセージの両方から最新のメッセージを取得したいです。

ユーザー情報のすべて＋unread(未読数)＋最新メッセージの送受信日としたいところが、
最新メッセージの取得がうまくいかず、古いものが表示されてしまっています。
どのように変更すればここに、そのユーザーごとの最新メッセージを追加できるでしょうか？

Comments

[yambejp]

サンプルを画像で貼るのはよいとして、あわせてテキストで貼ってください
create table+insert形式で書くと回答しやすいです

[dydo]

修正しました。こちらのほうが格段にわかりやすくなったと思います。ありがとうございます。

[yambejp]

f_relationにてaaa,bbbと裏返ったbbb,aaaがありますが
これは一方通行の友達関係が成り立つということでしょうか？

[kasa0]

('古いメッセージ', 'ccc', 'aaa', '2019-12-05 11:30:21', 0),
('新しいメッセージ', 'aaa', 'ccc', '2019-12-06 9:35:21', 1),
送信者が異なるから、両方新しいメッセージになるのでは？

[dydo]

登録時に自分から相手と、相手から自分両方追加するので、成り立たないです。修正しました。

[dydo]

>kano0さん
それも間違っているかしれません。
友達とのやり取りで、自分が送信したメッセージと、自分が受信したメッセージの両方から最新のメッセージを取得したいです。

Answer 1

JOINによる結合ではなく副問合せでやってみるのはいかがでしょうか？

SELECT
 u.*
 ,(SELECT COUNT(id) 
   FROM
     talk t 
   WHERE
     t.receiver = u.id
   AND
      COALESCE(read_flg, 0) = 0 -- nullの場合、0を返す
 ) AS unread
-- 以下、略

talkテーブルにインデックスを張ったり工夫すると、速くなるかと思います。

Comments

[dydo]

少し考えてみます