【SQL】ユーザーに紐づく投稿と投稿に紐づくコメント数・イイネ数の取得

WITH句を使って一本のSQLで実装する。

ユーザーの詳細画面遷移時にそのユーザーの投稿とその投稿に紐づくコメント数とイイね数を取得するAPIを作るためにSQL書きました、何本もSQL走らせるのよく無いよね・・と思い、調べたら WITH句という素晴らしいものがあったので使ってみました。

※ WITH句はMySQL 8系からのサポートです。

テーブル

投稿テーブル
POST
L ID
L TITLE
L TEXT

コメントテーブル
COMMENT
L ID
L POST_ID
L TEXT

イイねテーブル
REACTION
L ID
L POST_ID

WITH GET_COUNT AS ( -- GET_COUNT テーブルを作成
    SELECT 
    post_table.id as post_id,
    COUNT( comment_table.id ) AS comment_count
    COUNT( reaction_table.id ) AS reaction_count
    FROM post -- 投稿テーブルをベースとします。
    LEFT OUTER JOIN comment_table ON comment_table.post_id = post.id -- コメントテーブルを投稿IDで束ねる
    LEFT OUTER JOIN reaction_table ON reaction_table.post_id = post.id -- イイねテーブルを投稿IDで束ねる
    WHERE post_table.user_id = 150 -- ユーザーIDを指定する
    GROUP BY -- 投稿IDで束ねる
    post.id
) SELECT 
    post_table.id,
    post_table.tittle, 
    post_table.text, 
    GET_COUNT.comment_count,
    GET_COUNT.reaction_count
    FROM post_table
    INNER JOIN GET_COUNT ON post_table.id = GET_COUNT.post_id -- 投稿テーブルと GET_COUNT テーブルを紐付ける
    LIMIT 10 -- 10件取得
    OFFSET 0 -- 0〜10件目を取得;