Q&A
初めての質問で勝手がわかっていないので、
もし質問の内容に不備等があればご指摘いただければ幸いです。
前提
LINEのようなチャットのシステムを作っています。
SQL(mysql)で、以下の２つのテーブルを作りました。
chatspace（テーブル名）
・id
・created_at
...(その他のカラム)
comments（テーブル名）
・id
・chatspace_id
・body
・created_at
...（その他のカラム）
各chatspaceに複数のコメントが投稿されるという設計になっています。
（chatspace_idが同一のcommentsのレコードが複数存在する。）
実現したいこと
LINEのトーク一覧画面のような表示を作るために、
SQLで各chatspaceの情報（全てのカラム）と、
各chatspace中（comments.chatspace_idが同じレコード）の最新(comments.created_atが最大のレコード)の
comments.bodyとcomments.created_at AS commented_atを取得したいです。
JOINやサブクエリ等、関係しそうな情報を調べ試したのですが、実現できなかったのでご教示いただきたいです。
よろしくお願いいたします。
試したこと
・commentsテーブルで、GROUP BY chatspace_idで、idとMAX(created_at)を取得する。次に、これをサブクエリとしてchatspaceの情報を取得。
→
サブクエリの、
SQL
1SELECT chatspace_id, body, MAX(created_at) FROM comments GROUP BY chatspace_id;
の部分で、sql=only_full_group_byが必要というエラーが発生。
(bodyを指定しなければ取得可能。)
・INNER JOINで結合する
→comments.created_atが最大のレコードのみを取得することができない（GROUP BYを使うとsql=only_full_group_byが必要というエラーが発生）
・サブクエリとしてcommentsテーブルを取得しする。このときにWHERE句で＞（greater than sign）を使って最大値のみにする。
→
うまくいかなかったのですが、
参考にしたサイトを見失ってしまい、エラーを含め再現できなくなってしまいました。
(docker上で試し、downしてしまったのでログも辿れませんでした)
補足情報（FW/ツールのバージョンなど）
mysql
(最終的にはGORMを使用するので、SQLよりGORMの方が書きやすければGORMで示していただいても結構です。)
まず自身が試したことを記載されると良いかと。
調べただけでは実現できないのは当然で、何かしら試したうえで実現できなかったのであれば、
その内容をもって質問としてください。質問は編集できます。
あと「最新」だと１件にしかならないのでは？
コメントが編集可能で履歴を持っているわけではないですよね？
m.ts10806さんも書かれているように、質問者さんが考えるここでの「最新」の定義を明確にした方がアドバイスは集まりやすいと思います。例えば、「表示した時間の１時間前までに更新されたものを最新と考える」等追記してみてはどうでしょうか。
なんとなくやりたいことがわからないでもないですが、今のままだと推測でしか話ができないので、回答に二の足を踏む方が多いと思います。
「最新」の定義と、試したことを記載しました。
ご指摘ありがとうございます。他にも不備があればご指摘いただければ幸いです。
回答1件
1
ベストアンサー
order by でcreated_at降順にしてlimit 1
したのをJOIN
※構文チェッカー済みですが、実テーブルでの検証まではしていません。
例えば。
SQL
1select 2 s.* 3-- 適宜エイリアス付けてください。省略してるので 4 cc.body, 5 cc.created_at 6from 7 chatspace c 8 JOIN 9 ( 10 SELECT 11 chatspace_id, 12 body, 13 created_at 14 FROM 15 comments m 16 where 17 chatspace_id = c.chatspace_id 18 order by 19 created_at 20 limit 1 21 ) cc
MAXを活かすならこんな感じにも組めると思う。（もしかしたらこちらのほうが想定通りにとれるかもしれない）
MySQL8ならこんな感じ。
SQL
1-- 最新コメントIDリスト 2with latest_comments_id_list as( 3 SELECT 4 chatspace_id, 5 MAX(created_at) as latest_comments_created_at 6 FROM 7 comments 8 GROUP BY 9 chatspace_id 10), 11-- 各IDごとの最新コメント 12latest_comments as( 13 SELECT 14 latest_comments_id_list.chatspace_id, 15 c.body, 16 latest_comments_created_at 17 from 18 comments c 19 join 20 latest_comments_id_list 21 on latest_comments_id_list.chatspace_id = c.chatspace_id 22 and latest_comments_created_at = c.created_at 23) 24 25-- 各チャットルームの最新コメント 26select 27 c.*, 28 -- 適宜エイリアス付けてください。省略してるので 29 latest_comments.body, 30 latest_comments.created_at 31from 32 chatspace c 33 join 34 latest_comments 35 on latest_comments.chatspace_id = c.chatspace_id
MySQL5系ならWithのところをサブクエリでchatspace へjoinする形になります。
場合によってはSELECT句にサブクエリ付けてもいいかもしれませんが、
SQL1本でやらずに、
①チャットルームの一覧を取得してループ
②ループ内で最新コメント取得（ここはorder by + limit1のほうがいい）
と分けてやっても良いと思います。
投稿2022/09/24 08:14
編集2022/09/24 19:54総合スコア80695
回答へのコメント
MAXを活かすなら、のSQL文のやり方で設計通りに動作しました。
丁寧なご回答ありがとうございます。
また、質問内容の改善にもご協力いただきありがとうございました。
解説要らないですかね？
とりあえず動けばいいというだけだと、おそらく今後の回収難しいと思うのですが。
おそらく私であれば最後に書いた「一覧ループしつつぞれぞれ取る」方法でやります。
order byを使っているSQL文に関しては、7行目のc.chatspace_id(c.id?)が取得できないというエラー（スコープの問題？）が出て解決できませんでしたが、
MAXを活かすなら、のSQL文については
何をやっているか理解できました。（WITH句については初見だったので調べました。）
改変もできているので、認識の誤りもおそらくないと思われます。
一覧ループしつつぞれぞれ取る、については、
N+1問題で処理が重くなってしまう可能性があり、
また私の負荷テストの知見が浅いため避けました。
ただ、ページネーション機能をつける予定なので、
そこまで負荷が増えないと思われるためもう一度検討します。
ありがとうございます。
>order byを使っているSQL文
もしかしたらSELECT句に並べた方が良かったかもしれません。あの位置だと元テーブルが参照できないかな。
1つ理解につながったようで何よりです。
MySQLのバージョンによってはWITH使えないのでご注意ください。
＞ただ、ページネーション機能をつける予定なので、
となると、主となるテーブルはやはりチャットルーム一覧になりますね。
どのくらいの件数表示するとか、もう少し要件詰めてから実現方法を検討してみてください。
あなたの回答
tips
プレビュー
