[PHP][Laravel][SQL] LaravelのEloquentでFROM句の副問い合わせ

バージョン

Laravel 6.0

概要

Eloquentで以下を表現する。

SELECT tbl.a, tbl.b FROM (SELECT a,b FROM hoge) as tbl

実現したいSQL

SELECT
    user_id, sum(salary) as sum_salary
FROM (
    SELECT
        user_id, salary 
    FROM
        users
    WHERE
        user_type = 1
        ) AS target_user
WHERE
    target_user.salary > 300000
GROUP BY
    target_user.user_id

Models/user.php

$subquery = self::select(['user_id', 'salary'])
    ->where('user_type', '=', ':usertype')
    ->toSql();

return DB::table(DB::raw('('.$subquery.') AS target_user'))
    ->selectRaw('user_id, sum(salary) as sum_salary')
    ->setBindings([':usertype' => $user_type_val])
    ->where('target_user.salary', '>', 300000)
    ->groupBy('target_user.user_id')
    ->get()
    ->toArray();

PHPフレームワーク Laravel入門 第2版