Laravelのクエリビルダでサブクエリ(副問い合わせ)を使って集計する

Laravelでテーブルに外部結合先の複数テーブルを集計したいときに、
詰まったので備忘録として記載。

サブクエリ(副問い合わせ)

ユーザーテーブルと、予約テーブル、履歴テーブルがある場合を想定します。
(実テーブルはこちらを参照)

ユーザーテーブルのidと、予約テーブル、履歴テーブルのuser_idをキーとして結合します。

結論として、こうすると実現可能です。

        // 予約テーブル
        $user_reserve_query = Reservations::query()
            ->select(DB::raw('count(user_id) as r_count, user_id'))
            ->groupBy('user_id')
            ->toSql();

        // 予約履歴テーブル
        $user_history_query = History::query()
            ->select(DB::raw('count(user_id) as h_count, user_id'))
            ->groupBy('user_id')
            ->toSql();

        // 外部結合したユーザーテーブル
        $query = User::query()
            ->leftJoin(DB::raw('('.$user_reserve_query.') AS ur'), 'ur.user_id', '=', 'users.id')
            ->leftJoin(DB::raw('('.$user_history_query.') AS uh'), 'uh.user_id', '=', 'users.id')
            ->select('users.*','r_count','h_count')
            ->get();

注目すべき点は2点です。

まず、外部結合するテーブルのSQLを発行します。
DB::raw を使って、集計するSQLを記載します。
->select(DB::raw('count(user_id) as r_count, user_id'))

toSql() でSQLを発行します。
->toSql();

そして、[User]ユーザーテーブルに外部結合します。
User::query()
->leftJoin(DB::raw('('.$user_reserve_query.') AS ur'), 'ur.user_id', '=', 'users.id')

これで、

外部結合先のテーブルを集計した結果も含めて

テーブル情報として取得できるようになります！
これでもう詰まらないぞ・・・！！
ちなみに、実際になげてるSQLはこちらです。
SELECT * FROM users as u
left join (select user_id, count(user_id) as r_count
	from reservations group by user_id) as r
	on u.id = r.user_id
left join (select user_id, count(user_id) as h_count
	from history group by user_id) as s
	on u.id = s.user_id

実テーブル
この記事で使っているテーブルです。
[User]モデルで取得するユーザーテーブルです。



id
name




1
田中 太郎


2
鈴木 二郎


3
佐々木 三郎



[Reservations]モデルで取得する予約テーブルです。

user_idをキーとしてユーザーテーブルのidと外部結合します。



id
user_id
description




1
2
鈴木さんが予約


2
2
鈴木さんが予約


3
1
田中さんが予約



[History]モデルで取得する履歴テーブルです。

user_idをキーとしてユーザーテーブルのidと外部結合します。



id
user_id
history




1
2
予約済み


2
2
予約済


3
1
キャンセル














あんこ
月の半分は出張生活の技術営業をこなす日々から、

気づけばフリーエンジニアに転身し、活き活きとした毎日を送るように！
猫の下僕。

ネコワーキングスペース 創りたい。
関わった人が活躍できる場所を創るために奔放中！

＼あんこのTwitterはこちら！／