Laravel DB Query Builder "whereNotIn" to another table

Question

How i can use query builder to condition whereNotIn to an other table without using DB::raw();

$query ="select * from project 
            where prj_usr_id= $user->id 
            and now()<prj_expiry 
            and prj_id not in(select bd_prj_id from bid where bd_status=1) 
            and prj_status='open' 
            order by prj_updated_date desc;

possible duplicate of http://stackoverflow.com/questions/16815551/how-to-do-this-in-laravel-subquery-where-in — herrjeh42, Jun 26 '16 at 08:52

score 0 · Accepted Answer · answered Jun 26 '16 at 10:27

I solved by this.

$results = DB::table('project')
                        ->where('prj_usr_id',  '=' , 1 ) 
                        ->where('prj_status',  '=' , 'open' ) 
                        ->where('prj_expiry',  '<' , Carbon::Now() ) 
                        ->whereNotIn ('prj_id',function ($query)
                        {
                            $query->select(DB::raw(1))
                                    ->from('bid')
                                    ->where('bd_status',  '=' , '1' ) 
                                    ->get(['bd_prj_id']); 
                        })
                        ->orderBy('prj_updated_date', 'desc') 
                        ->paginate(5);
return  $results;

Laravel DB Query Builder "whereNotIn" to another table

1 Answers1