sub query in laravel where clause

Question

I want to get write sub query in where clause as in mysql.Is it possible to use sub query in where clause in laravel

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')

In laravel I have tried as

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();

Sagar Jajoriya · Accepted Answer · 2017-10-24 07:09:29Z

3

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();

answered Oct 24, 2017 at 7:09

Sagar Jajoriya

2,3751 gold badge12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2017-10-24 07:11:05Z

I think you can refactor your query to replace the subquery in the WHERE clause with another join:

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
INNER JOIN asset_request ar
    ON p.code = ar.project_code
WHERE
    ar.request_id = '1718AM0010'

Laravel code:

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')
            ->join('asset_request as ar', 'ar.project_code', '=', 'p.code')
            ->where('ar.request_id', '=', '1718AM0010')
            ->select('u.id')
            ->first();

Collectives™ on Stack Overflow

sub query in laravel where clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related