GROUP BY を使った時の ORDER BY の動きに関しまして。

Question

現在、下記のようなDB構成の既存メールシステムをLINEのようなチャットシステムに変更してます。

|id|to_user_id|from_user_id|to_user_flg|from_user_flg|send_date|
|:--|:--:|--:|--:||--:|
|ユニーク(インクリメント)|受信者ID|送信者ID|受信者フラグ|送信者フラグ|送信日時|

DBの構成を変えないで呼び出し方を変えて変更したいと考えています。

受信者フラグは下記のようになっています。
to_user_flg
0: 削除済み
1: 未読
2:既読

※グループチャットはありません。

上記のようになっています。

やりたいことはチャットルーム一覧ページのソートで

やり取りしているユーザーIDの小さい方を先に文字列連結して
ルームIDを作っています。
ルームIDでグループ化してやり取りユーザ毎に取得できるようにしています。
問題はソートで
（未読、既読、返信済み）ANDメールIDの降順でソートしたいのですが
うまくソートできません。おそらくGROUP BYで持ってきてるSORTが最後のやり取りから取得できてないが
原因だと思いますが意思通り取得できる方法はありませんでしょうか。

最悪、テーブルは追加できます。

ユーザーIDは一覧ページを取得するユーザのIDが入ります。

SELECT MAX(id) AS max_id, 
(CASE WHEN (from_user_id < to_user_id) 
THEN CONCAT(from_user_id, to_user_id)
 ELSE CONCAT(to_user_id, from_user_id) 
END) AS ROOMID,
 (CASE WHEN (to_user_id = 「ユーザID」 AND to_user_flg=1) 
THEN 0 #未読
WHEN (to_user_id = 「ユーザID」) 
THEN 1  #既読
ELSE 2   #返信済み
END) AS SORT 
FROM mail_data 
WHERE (to_user_id = 「ユーザID」 OR from_user_id = 「ユーザID」)
 AND to_user_flg != 0 
GROUP BY ROOMID ORDER BY SORT ASC, max_id DESC

よろしくお願い致します。

Answer 1

やりたい事は、恐らく、
送受信したチャットを１：１のチャット相手ごとに、
未読順、最後の送受信順にソートしたいように見受けました。

が、色々とおかしいので、いくつか指摘だけ。

（１）

|to_user_flg|from_user_flg|

|:--|--:|
|送信者フラグ|受信者フラグ|

受信者フラグは下記のようになっています。

to_user_flg

⇒言いたい事は分かりますが、見直し（修正）してくださいね。

（２）

「from_user_id|」

⇒最後の「|」は何でしょう？（他の項目も）

（３）

CONCAT(to_user_id|, from_user_id|)

⇒WHERE句にidを指定しているので、バグではないですが、
to_user_idとfrom_user_idの間に何かしらの数値以外
（「|」がその役割？）を含めないと、
「12と34のチャット」と「1と234のチャット」が同一になります。

（４）

from_user_flg = 「ユーザID」

⇒from_user_idの間違えでは？

（５）

(CASE WHEN (to_user_id = 「ユーザID」 AND to_user_flg=1)

THEN 0 #未読
WHEN (to_user_id = 「ユーザID」)
THEN 1 #既読
ELSE 2 #返信済み

⇒このCASE文でやりたい事はそもそも何でしょう？

　「未読が１件でもあれば0？最後が未読なら0？
受信が１件でもあれば1？最後が受信で止まってたら1？
最後が送信者なら2？」

最初に記載した「未読順、最後の送受信順」であれば、
1と2は区別の必要はない気もしつつ。
記載されたSQLから読み取ると、
a:「昔の受信で、相手の送信で終わってるもの」
b:「最近送信し、返信待ちのもの」
があった際、aを先に表示するってことですかね？

（６）

jushinn_op_flg != 0

⇒この項目は？
to_user_flg　と　from_user_flg　が0以外？

（７）

おそらくGROUP BYで持ってきてるSORTが最後のやり取りから取得できてない

⇒記載されたSQLのGROUP BY にはSORTが含まれてませんので、
現在のSORTの中身は、適当に返された値です。
また、日本語で「最後のやり取りから」に該当する部分の
SQLの記載がありません。

　MAX(id)に対して、mail_data テーブルのidと紐づけが必要です。

意思通り取得できる方法はありませんでしょうか。

⇒CASE文やEXISTS句、副問合せ等を利用すれば、方法はいくらでもあります。
（「１件でもあれば～」なら、EXISTS句、
「最後の送受信が～」なら、副問合せが必要です）

（８）

最悪、テーブルは追加できます。

⇒全体が不明なので、判断はできませんが、
「１：１のチャット毎」ではなく、「ルーム（部屋）毎」という表現に
こだわりがあるのであれば、
ルームテーブルが必要な気はします。

Comments

[HARIBOYA]

回答有り難うございます。
ありがたいです！

説明にミスがあり申し訳ございませんでした。
１〜４,6に関しては単純な記載ミスでしたので修正いたしました。

＞（５）このCASE文でやりたい事はそもそも何でしょう？
一覧で
受信が最後で未読があった場合： 優先１
受信が最後の場合未読でない場合： 優先２
送信が最後の場合：　　　　　　　 優先３

各優先毎に送信日時順で一覧を作りたいです。

＞（７）⇒記載されたSQLのGROUP BY にはSORTが含まれてませんので、
＞現在のSORTの中身は、適当に返された値です。
＞また、日本語で「最後のやり取りから」に該当する部分の
>SQLの記載がありません。

「SELECT MAX(id) AS max_id」で最後になると思っていましたが
それと「HAVING MAX(id)=id」を入れたのですが１番最後のidには何故かなりませんでした。

＞(８)⇒全体が不明なので、判断はできませんが、
＞「１：１のチャット毎」ではなく、「ルーム（部屋）毎」という表現に
＞こだわりがあるのであれば、
＞ルームテーブルが必要な気はします。

ルームにこだわりわ無いです。リストで表示した時に同じユーザ間で纏めて出せて
ソートがしっかりしていれば良いです。

Answer 2

group by に SORTの項目を追加して下さい。
mysqlはデフォルトの設定では、group byに指定が無い項目は、忖度して適当な値を返す仕様ですので。

caseの内容とか、条件については違和感を覚えますけど、敢えて触れません。

追記

送信者と受信者のペアで固めるのにユーザーIDを使用するなら、どちらが先に来ても同じものにしなければなりません。

同じ行内の項目の最大/最小を求めるGREATEST/LEASTを利用すれば簡単に判断できます。
12.3.2 比較関数と演算子
取り敢えず以下のようにすれば、送信者と受信者のペアで固まって並ぶはずですから、既読/未読の条件を追加してみて下さい。

SELECT *
FROM mail_data 
ORDER BY  LEAST(from_user_id , to_user_id)
        , GREATEST(from_user_id , to_user_id) 
        , id

Comments

[HARIBOYA]

group byにSORTを追加するって事はROOMIDでSORTが違う場合に重複してしまいませんでしょうか？

[HARIBOYA]

>caseの内容とか、条件については違和感を覚えますけど、敢えて触れません。

勉強のため突っ込んでほしいです。
おねがいしますm(_ _)m

[sazi]

> ROOMIDでSORTが違う場合に重複
そうなるなら、roomidまたはsortを作り出す仕様に考慮が不足しているんでしょう。
sortはto_user_flgそのままで、ROOMIDを(小さいID＋大きいID）だけになるようにすれば問題なさそうです。

[HARIBOYA]

お返事ありがとうございます。
感謝です。

>sortはto_user_flgそのままで、ROOMIDを(小さいID＋大きいID）だけになるようにすれば問題なさそうです。
to_user_flgは自分が受信者になる場合もある為、相手が受信者になる場合もあるのでto_user_flgだけでソートが出来ない状態です。

(to_user_flg = 「ユーザID」 OR from_user_flg = 「ユーザID」)

>そうなるなら、roomidまたはsortを作り出す仕様に考慮が不足しているんでしょう

そうだとは思いますが良い方法が思いつかない状態です。

[sazi]

送信者が未読/既読とはどういう状態なんでしょう？

[HARIBOYA]

お返事ありがとうございます。

＞送信者が未読/既読とはどういう状態なんでしょう？
送信者が未読/既読フラグではなくどっちが送信したかによって「to_user_flg」の対象が変わります。
A（自分）
B（相手）

Aから送信した場合にはto_user_idはBのIDが入ります。
Bから送信した場合にはto_user_idはAのIDが入ります。

受信一覧ではなくルーム一覧なのでA,Bどっちが送信した場合も一覧には表示したいです。

説明ベタですみません。ｌ

[HARIBOYA]

追記有難うございます！

ちょっと考えてみます！

[sazi]

未読/既読はメッセージに対してのものですから、送信/受信それぞれに持たせているというなら、違和感しかない。

[HARIBOYA]

＞未読/既読はメッセージに対してのものですから、送信/受信それぞれに持たせているというなら、違和感しかない。

すみません、多分私の説明が悪くてちゃんと伝わってないのだと思います。
送信に対して未読、既読があるのでわなくto_userの対象が
A,B間のやり取りの場合にAが送信していればto_userの対象がB
Bが送信者ならばto_userはAが対象になります。

to_user_flg
0: 削除済み
1: 未読
2:既読

from_user_flg
0：削除済み
1：保持

|id|to_user_id|from_user_id|to_user_flg|from_user_flg|send_date|
|:--|:--:|--:|--:||--:|
|1|B|A|2|1|2019/10/11 19:02|
|2|B|A|2|1|2019/10/11 20:00|
|3|A|B|1|1|2019/10/11 20:01|

たとえば,ID 1はto_userの対象がBです。