状況がピンポイントすぎるのですがたまーに有り得そうだなと思い、自分の備忘録も兼ねて記事を書きました。
いろいろなテーブルに対して異なる条件で withCount を大量に行ったため、処理が重く画面が表示できない(リクエストがタイムアウトする)状態だったので~~可読性を犠牲にして~~高速化した話です。

「半生SQL」と書いたのは使えるところは Laravel を使ったからです。

処理概要

イメージとしては以下のような 1 対多の関係のテーブルがあり、特定のステータスの変更依頼がある注文の数をカウントするという処理でした。

上記の2つのテーブルを結合した場合以下のようになります。

注文.ID	注文変更依頼.ID	注文変更依頼.ステータス
1	10	依頼中
1	11	対応済み
1	12	依頼中
2	13	依頼中

変更依頼同時に複数出せるんかいというツッコミはおいておいて(あくまでイメージなので)、紐づく注文変更依頼は複数あります。
カウントしたいのは「「依頼中」のステータスの注文変更依頼が紐付いている注文が何件あるか？」であるため、「依頼中」の注文変更依頼が何件あろうとも紐付いてさえいれば1件としてカウントします。

つまり、上記の例では件数は以下のようになります。
依頼中がある注文：2件
対応済みがある注文：1件

このように単純に「注文変更依頼」の数をカウントするのではなく、あくまで「注文」の件数をカウントしないといけないのがややこしいところでした。

対応

変更前の実装

先に変更前の実装のイメージ書いておきます。

   $query
         ->withCount(['changeRequests as status_irai_tyu' => function ($q) {
            $q->where('status', ChangeRequest::STATUS_IRAI_TYU);
        }])
        ->withCount(['changeRequests as status_taiou_zumi' => function ($q) {
            $q->where('status', ChangeRequest::STATUS_TAIOU_ZUMI);
        }])
  // 以下に10個くらい続く

Eloquent で素直に書こうと思ったらこうなりますよね、という感じでした。
いざステージングで動かしたらデータ件数が多くタイムアウトしてしまったようです。
(念の為ですが上記のようなコードを書いたのは私ではないです…！)

対応方針

紐づくテーブルが 1対多になっているのがやりづらいのでじゃあ 1対1 になるようにしよう、ということで以下のように該当するステータスの注文変更依頼があればカラムを1に(フラグを立てる)なっているようなテーブルを作ります。

注文.ID	ステータス依頼中	ステータス対応済み
1	1	0
2	0	0
3	0	1

このようなテーブルに対してステータスごとにカウントするSQLを書けばできそうです。
以後このテーブルを「注文変更依頼ステータス」テーブルと呼びます。

「注文変更依頼ステータス」テーブルを作る

以下のSQLで「注文変更依頼ステータス」テーブルを作ることができます。

SELECT
  order_id,  -- 注文ID
  MAX(CASE WHEN status = 0 THEN 1 ELSE 0 END) as status_irai_tyu, -- ステータス依頼中
  MAX(CASE WHEN status = 1 THEN 1 ELSE 0 END) as status_taiou_zumi, -- ステータス対応済み
  <中略>
FROM  change_request
GROUP BY order_id;

ステータスフラグになるカラムを作成するこのSQLを簡単に解説します。

MAX(CASE WHEN status = 0 THEN 1 ELSE 0 END) as status_irai_tyu

MAX関数の仲のCASE文で
特定のステータスのときに 1 (フラグON)、
それ以外のときは 0(フラグOFF)となるようにします。
GROUP BY order_idで注文IDでグルーピングされるので、そのグループの中でMAXを取ることによって、ひとつでも 1 があればその注文IDのstatus_irai_tyuカラムは 1 になります。
どれか1つでも TRUE であればよい OR 演算と同じです。

テキストだけだと少しわかりづらいので具体的なテーブルを書きました。
記事の先頭に出したテーブルを CASE 文でフラグに変換した場合以下のようになります。
※これは GROUP BY でまとめる前として見てください

注文.ID	注文変更依頼.ID	ステータス依頼中	ステータス対応済み
1	10	1	0
1	11	0	1
1	12	1	0
2	13	1	0

この表を GROUP BY と MAX で注文IDごとにまとめるとこうなります。

注文.ID	ステータス依頼中	ステータス対応済み
1	1	1
2	1	0

注文ID:1 はステータスが「依頼中」の変更依頼が2件あるんですが MAX で最大値を取っているので結局 1 になります。

これで「注文テーブル」に対して 1対1 となる「注文変更依頼ステータス」テーブルができました！

結合＆合計

最後に「注文テーブル」と「注文変更依頼ステータス」テーブルを結合して合計していきます。

SELECT
  SUM(status_irai_tyu) as irai_tyu_count, -- ステータス依頼中の合計
  SUM(status_taiou_zumi) as taiou_zumi_count, -- ステータス対応済みの合計
  <中略>
FROM order
JOIN (
  SELECT
    order_id,  -- 注文ID
    MAX(CASE WHEN status = 0 THEN 1 ELSE 0 END) as status_irai_tyu, -- ステータス依頼中
    MAX(CASE WHEN status = 1 THEN 1 ELSE 0 END) as status_taiou_zumi, -- ステータス対応済み
    <中略>
  FROM  change_request
  GROUP BY order_id;
) as change_request_status ON change_request_status.order_id = order.id;

最終的には以下のような各ステータスの注文件数のテーブルになります。

ステータス依頼中件数	ステータス対応済み件数
2	1

どんどん行を減らしていく作業でしたね。

Laravel に書き起こす

~~いやー終わった終わった~~ 生の SQL をべたっと貼り付けるわけにはいかないのでできるだけ Eloquent に変換します。

上で書いた最終的な SQL を Laravel で書くとこのようになります。
※ChangeRequest Order はそれぞれテーブルと対応するモデルです。

$changeRequestStatusTable = ChangeRequest::query()
    ->selectRaw('order_id')
    ->selectRaw('MAX(CASE WHEN status = 0 THEN 1 ELSE 0 END) as status_irai_tyu')
    ->selectRaw('MAX(CASE WHEN status = 1 THEN 1 ELSE 0 END) as status_taiou_zumi');
    
Order::query()->joinSub(
    $changeRequestStatusTable,
    'change_request_status',
    fn ($join) => $join->on('change_request_status.order_id', '=', 'order.id')
)
    ->selectRaw('SUM(status_irai_tyu) AS status_irai_tyu_count')
    ->selectRaw('SUM(status_taiou_zumi) AS status_taiou_zumi_count');

※ステータスの判定している箇所は実際は数値ではなくて定数を使っています

感想

タイムアウトするくらいの重さだったページが1~2秒で表示されるようになったのはやっぱり嬉しいですね！

そして以前に複雑なビューテーブルを作ったときもお世話になったのですが、ミック先生のSQL指南書が今回も役に立ちました！

達人に学ぶ SQL徹底指南書 | ミック | 工学 | Kindleストア | Amazon

まだ EXISTS などは全然使いこなせていないのですがこの本で知った CASE 文だけでもかなり役に立ってるのでそれだけでも元が取れたと感じます。

余談

www.lucidchart.com

ER図の作成を上記のツールを使ってみました。
今回のような書き捨てのような図であればさくっとできてよかったです。

ひでメモ

プログラムについて勉強したことを書きます。たぶん。

【SQL・Laravel】大量の withCount を半生SQLに書き換えて高速化した話