How to make sql nested/sub query in PHP

I’m struggling with implementing SQL query into my php code.

The query:

select template_id, min(created_at) as created_at from (
select distinct 
root_template_id as template_id, 
date_created as created_at, 
response_id as response_id
from db.score
inner join db.users ON db.score.supplier_id = db.users.old_id
inner join db.acc ON db.acc.user_id = db.users.id
where db.acc.account_id = 25) as T
group by template_id

I was able to prepare only this part:

$query = DB::table('score')
->selectRaw('DISTINCT root_template_id as template_id, date_created as created_at, response_id') 
->join('users', 'score.supplier_id', '=', 'users.old_id')
->join('acc', 'acc.user_id', '=', 'users.id')
->whereIn('acc.account_id', $request->id)

And it works, but it is only responsible for the nested part, for the subquery if I can call it like this. Can someone share any thoughts?

Answer

DB::table() usually receive table name as a string, but it can also receive Closure or an IlluminateDatabaseQueryBuilder instance, so this should do the trick for Laravel’s select from subquery:

DB::table(function ($query) use ($request) {
    $query->selectRaw('DISTINCT root_template_id as template_id, date_created as created_at, response_id')
    ->from('score')
    ->join('users', 'score.supplier_id', '=', 'users.old_id')
    ->join('acc', 'acc.user_id', '=', 'users.id')
    ->whereIn('acc.account_id', $request->id)
}, 'T')
    ->select([
        'template_id',
        DB::raw("MIN(created_at) AS created_at"),
    ])
    ->groupBy('template_id')
    ->get();