【SQL】チャットで各ユーザから受信した最新メッセージのみを表示する #SQL

概要

あるユーザAさんが、B～Zさんとメッセージをやり取りしている場合、B～Zさんの最新メッセージのみを一覧で表示したい。チャット ~~（lineとか）~~ でよくあるパターン。

名前	日時	最新メッセージ
Bさん	22:10	久しぶり
Cさん	12:00	宿題忘れた
Dさん	9:20	起きてる？
Eさん	5:20	二重の極み！

テーブル

テーブル : chat_list

カラム	型	意味
chat_id	INTEGER	チャットid
user_id_from	INTEGER	ユーザid（送信者）
user_id_to	INTEGER	ユーザid（受信者）
message	TEXT	メッセージ
datetime_send	DATETIME	送信日

クエリ

イメージ的には、送信者一覧とメッセージの最新送信日時を取得してから、それにLEFT JOINで最新メッセージを付ける感じ。
GROUP BY - MAXで最新送信日時を取得する。
チャットidで管理したかったが、難しいかな。


 SELECT 
   /* メール送信者一覧 */
     tm.user_id_from
   , tm.user_id_to
   , tm.datetime_send_last 
   /* 最新のメッセージ（ユーザごと）*/
   , ts.chat_id      
   , ts.user_id_from  
   , ts.user_id_to    
   , ts.message     
   , ts.datetime_send
 FROM 
   /* メール送信者一覧 */
   ( 
     SELECT 
         tb.user_id_from       AS user_id_from
       , tb.user_id_to         AS user_id_to 
       , MAX(tb.datetime_send) AS datetime_send_last  /* 最新送信日時 */
     FROM 
       chat_list tb 
     WHERE 
       tb.user_id_to = 'my_user_id'  /* 修正する */
     GROUP BY
         tb.userIdFrom 
       , tb.userIdTo 
     ORDER BY 
       datetime_send_last DESC 
   ) tm 
   /* 最新のメッセージ（ユーザごと）*/
   LEFT JOIN ( 
     SELECT 
         tb.chat_id 
       , tb.user_id_from 
       , tb.user_id_to 
       , tb.message 
       , tb.datetime_send
     FROM 
       chat_list tb 
     ORDER BY 
       tb.datetime_send DESC 
   ) ts_1 ON     ts_1.user_id_from  = tm.user_id_from 
             AND ts_1.user_id_to    = tm.user_id_to   
             AND ts_1.datetime_send = tm.datetime_send_last

ダメな例

下記でも出来るかと思ったが、サブクエリの中ではlimitを使用できない仕様らしい。
なので、正しく動作しない。
考え方的には似ているが、~~group byには軽いトラウマがあるので、~~ 出来ればこちらを使用したかった。
（MySQLを想定）

/* クエリ */
SELECT
  /* メール送信者一覧 */
    tm.user_id_from
  , tm.user_id_to
  /* 最新のメッセージ（ユーザごと） <- レコードが無い場合は、null */
  , ts.chat_id     
  , ts.user_id_from
  , ts.user_id_to  
  , ts.message     
  , ts.datetime_send
FROM
  /* メール送信者一覧 */
  (
    SELECT DISTINCT
        user_id_from
      , user_id_to
    FROM
      chat_list
    WHERE
      user_id_to = 'my_user_id'  /* 修正する */
    ORDER BY
      datetime_send DESC
  ) tm
  /* 最新のメッセージ（ユーザごと） */
  LEFT JOIN (  /* レコードが無い場合は、nullが1行表示される */
    SELECT
        chat_id
      , user_id_from
      , user_id_to
      , message
      , datetime_send
    FROM
      chat_list
    ORDER BY
      datetime_send DESC
    LIMIT 1 OFFSET 0  /* <-- この部分がNG */
  ) ts ON     ts.user_id_to   = tm.user_id_to   
          AND ts.user_id_from = tm.user_id_from
ORDER BY
  ts.datetime_send DESC

その他

仕様の部分で、少し手間取った。
最初にメインテーブルを決めて、それにLEFT JOINでデータを繋げるパターンが多いと思うけど、今回はメインテーブルがクエリだというケース。

group byを使っているので、データ数が増えると遅くなるかもしれない。
その場合は、別途、最新メッセージテーブルなどを作る必要があるかな？