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

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 キャンセル

関連記事一覧

  • コメント ( 0 )

  • トラックバックは利用できません。

  1. この記事へのコメントはありません。