質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.77%

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 326

HARIBOYA

score 19

現在、下記のような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 = 「ユーザIDAND 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 = 「ユーザIDOR from_user_id = 「ユーザID」)
 AND to_user_flg != 0 
GROUP BY ROOMID ORDER BY SORT ASC, max_id DESC

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+1

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/10/12 23:33

    追記有難うございます!

    ちょっと考えてみます!

    キャンセル

  • 2019/10/13 01:34

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

    キャンセル

  • 2019/10/13 21:48

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

    すみません、多分私の説明が悪くてちゃんと伝わってないのだと思います。
    送信に対して未読、既読があるのでわなく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です。

    キャンセル

+1

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

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

(1)

to_user_flg from_user_flg
送信者フラグ 受信者フラグ

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

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

(2)

「from_user_id|」

⇒最後の「|」は何でしょう?(他の項目も)

(3)

CONCAT(to_user_id|, from_user_id|)

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

(4)

from_user_flg = 「ユーザID」

⇒from_user_idの間違えでは?

(5)

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

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

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

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

(6)

jushinn_op_flg != 0

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

(7)

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

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

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

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

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

(8)

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/10/13 21:29 編集

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

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

    >(5)このCASE文でやりたい事はそもそも何でしょう?
    一覧で
    受信が最後で未読があった場合: 優先1
    受信が最後の場合未読でない場合: 優先2
    送信が最後の場合:        優先3

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

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

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

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

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.77%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る