mysql - laravel where子句中的子查询

我想在where子句中得到write子查询，就像在mysql中一样

SELECT
    p.*, d.name, d2.name, d2.head, u.id, u.aceid, u.firstname
FROM project as p
left join department as d
    on p.department_string_id = d.string_id
left join department as d2
    on d.parent_department_string_id = d2.string_id
LEFT join users as u
    on d2.head_aceid = u.aceid
where p.code in
(select ar.project_code from asset_request as ar
 where ar.request_id= '1718AM0010')

在拉腊维尔我试过

DB::table('project as p')
                ->leftJoin('department as d','p.department_string_id','=','d.string_id')
                ->leftJoin('department as d2','d.parent_department_string_id','=','d2.string_id')
                ->leftJoin('users as u','d2.head_aceid','=','u.aceid')
                ->where('p.code','=','USA_0057_07')  //instead of code need to use subquery
                ->select('u.id')
                ->first();

最佳答案

DB::table('project as p')
    ->leftJoin('department as d','p.department_string_id','=','d.string_id')
    ->leftJoin('department as d2','d.parent_department_string_id','=','d2.string_id')
    ->leftJoin('users as u','d2.head_aceid','=','u.aceid')
    ->whereRaw("p.code in (select ar.project_code from asset_request as ar where ar.request_id= '1718AM0010')")
    ->select('u.id')
    ->first();