チャットグループの最新メッセージを取得した上でグループの一覧を取得するSQLクエリ

Question

前提・実現したいこと

MySQLを使用しています。
テーブル構造はこのような形です。
userテーブル

+-------------+-------------------------+------+-----+----------------------+--------------------------------+
| Field       | Type                    | Null | Key | Default              | Extra                          |
+-------------+-------------------------+------+-----+----------------------+--------------------------------+
| id          | varchar(36)             | NO   | PRI | NULL                 |                                |
| email       | varchar(500)            | YES  |     | NULL                 |                                |
| name        | varchar(500)            | YES  |     | NULL                 |                                
| password    | varchar(500)            | NO   |     | NULL                 |                                |
| created_at  | datetime(6)             | NO   |     | CURRENT_TIMESTAMP(6) |                                |
| updated_at  | timestamp(6)            | NO   |     | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP(6) |
|
+-------------+-------------------------+------+-----+----------------------+--------------------------------+

messageテーブル

+---------------+--------------------------------------+------+-----+----------------------+--------------------------------+
| Field         | Type                                 | Null | Key | Default              | Extra                          |
+---------------+--------------------------------------+------+-----+----------------------+--------------------------------+
| id            | int(11)                              | NO   | PRI | NULL                 | auto_increment                 |
| text       | text                                 | YES  |     | NULL                 |                                |
| created_at    | datetime(6)                          | NO   |     | CURRENT_TIMESTAMP(6) |                                |
| updated_at    | timestamp(6)                         | NO   |     | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP(6) |
| chat_group_id | int(11)                              | YES  | MUL | NULL                 |                                |
| sender_id     | varchar(36)                          | YES  | MUL | NULL                 |                                |
+---------------+--------------------------------------+------+-----+----------------------+--------------------------------+

groupテーブル

+------------+--------------+------+-----+----------------------+--------------------------------+
| Field      | Type         | Null | Key | Default              | Extra                          |
+------------+--------------+------+-----+----------------------+--------------------------------+
| id         | int(11)      | NO   | PRI | NULL                 | auto_increment                 |
| name       | varchar(255) | YES  |     | NULL                 |                                |
| image_url  | text         | NO   |     | NULL                 |                                |
| created_at | datetime(6)  | NO   |     | CURRENT_TIMESTAMP(6) |                                |
| updated_at | timestamp(6) | NO   |     | CURRENT_TIMESTAMP(6) | on update CURRENT_TIMESTAMP(6) |
+------------+--------------+------+-----+----------------------+--------------------------------+

userとgroupの中間であるuser_chat_group_participationテーブル

+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| user_id       | varchar(36) | NO   | PRI | NULL    |       |
| chat_group_id | int(11)     | NO   | PRI | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

発生している問題・エラーメッセージ

この構成で、ユーザーが所属しているグループを最新メッセージのみ結合して取得したいのですが、
最新メッセージのみ結合するクエリがどうしても思いつきません。
お力添えいただきたいです。

Comments

[sazi]

求めたい結果や条件を明示して下さい。
条件や駆動表を何にするかでコストも変わりますから。

Answer 1

※テストデータもないので机上で考えただけです。無検証なのでご承知おきを。
※MySQL 8.0以上限定です。

次のような順序でクエリを組み立てましょう。
①指定ユーザーが所属するグループの一覧を取得

select user_id, chat_group_id 
from user_chat_group_participation
where user_id = [指定のユーザーID]

このクエリの結果をQ1とします。
② Q1で抽出したグループIDに紐づくメッセージを結合する。以下の例では全件結合している。
最新更新日時順に並びます。

select
  Q1.chat_group_id 
  ,T1.id
  ,T1.text
  ,T1.updated_at
from
  Q1
  JOIN message T1
  ON T1.chat_group_id == Q1.chat_group_id 
order by update_at desc

③ ②のクエリに「chat_group_idごとに、更新順順位をつける」ウィンドウ関数を適用します。
グループごとの更新順がupdate_order_by_chat_group_idに入ります。

select
  Q1.chat_group_id 
  ,T1.id
  ,T1.text
  ,T1.updated_at
  ,row_number() over ( partition by Q1.chat_group_id, T1.updated_at order by T1.updated_at) as update_order_by_chat_group_id
from
  Q1
  JOIN message T1
  ON T1.chat_group_id == Q1.chat_group_id 
order by chat_group_id, update_at desc

このクエリをQ2とします。

④ Q2の結果からupdate_order_by_chat_group_idが1のものを抽出すれば、グループごとの最新メッセージが取得できます。

select
  *
from
  Q2
where
  update_order_by_chat_group_id = 1

⑤　上記の各クエリをwith句で接続して完成です。

with Q1 as (
  select user_id, chat_group_id 
  from user_chat_group_participation
  where user_id = [指定のユーザーID]
), Q2 as (
  select
    Q1.chat_group_id 
    ,T1.id
    ,T1.text
    ,T1.updated_at
    ,row_number() over ( partition by Q1.chat_group_id, T1.updated_at order by T1.updated_at) as update_order_by_chat_group_id
  from
    Q1
    JOIN message T1
    ON T1.chat_group_id == Q1.chat_group_id 
  order by chat_group_id, update_at desc
)
select
  *
from
  Q2
where
  update_order_by_chat_group_id = 1

row_numberの使い方をきちんと理解できればクエリの組み立ては難しくないでしょう。