Laravel Eloquent "WHERE NOT IN"

Question

I'm having trouble to write query in laravel eloquent ORM.

my query is

SELECT book_name,dt_of_pub,pub_lang,no_page,book_price  
FROM book_mast        
WHERE book_price NOT IN (100,200);

Now I want to convert this query into laravel eloquent.

Answer 1

Query Builder:

DB::table(..)->select(..)->whereNotIn('book_price', [100,200])->get();

Eloquent:

SomeModel::select(..)->whereNotIn('book_price', [100,200])->get();

Answer 2

You can use WhereNotIn in following way also:

ModelName::whereNotIn('book_price', [100,200])->get(['field_name1','field_name2']);

This will return collection of Record with specific fields

Answer 3

I had problems making a sub query until I added the method ->toArray() to the result, I hope it helps more than one since I had a good time looking for the solution.

Example

DB::table('user')                 
  ->select('id','name')
  ->whereNotIn('id', DB::table('curses')->select('id_user')->where('id_user', '=', $id)->get()->toArray())
  ->get();

Answer 4

Query Builder:

    DB::table('book_mast')
->select('book_name','dt_of_pub','pub_lang','no_page','book_price')
    ->whereNotIn('book_price', [100,200])->get();

Eloquent:

BookMast::select('book_name','dt_of_pub','pub_lang','no_page','book_price')
->whereNotIn('book_price', [100,200])->get();

Answer 5

The dynamic way of implement whereNotIn:

 $users = User::where('status',0)->get();
    foreach ($users as $user) {
                $data[] = $user->id;
            }
    $available = User::orderBy('name', 'DEC')->whereNotIn('id', $data)->get();

Answer 6

You can use this example for dynamically calling the Where NOT IN

$user = User::where('company_id', '=', 1)->select('id)->get()->toArray();

$otherCompany = User::whereNotIn('id', $user)->get();

Answer 7

You can use WhereNotIn in the following way:

$category=DB::table('category')
          ->whereNotIn('category_id',[14 ,15])
          ->get();`enter code here`

Answer 8

You can do following.

DB::table('book_mast') 
->selectRaw('book_name,dt_of_pub,pub_lang,no_page,book_price')  
->whereNotIn('book_price',[100,200]);

Answer 9

This is my working variant for Laravel 7

DB::table('user')                 
  ->select('id','name')
  ->whereNotIn('id', DB::table('curses')->where('id_user', $id)->pluck('id_user')->toArray())
  ->get();

Answer 10

Its simply means that you have an array of values and you want record except that values/records.

you can simply pass a array into whereNotIn() laravel function.

With query builder

$users = DB::table('applications')
                    ->whereNotIn('id', [1,3,5]) 
                    ->get(); //will return without applications which contain this id's

With eloquent.

$result = ModelClassName::select('your_column_name')->whereNotIn('your_column_name', ['satatus1', 'satatus2']); //return without application which contain this status.

Answer 11

$created_po = array();
     $challan = modelname::where('fieldname','!=', 0)->get();
     // dd($challan);
     foreach ($challan as $rec){
         $created_po[] = array_push($created_po,$rec->fieldname);
     }
     $data = modelname::whereNotIn('fieldname',$created_po)->orderBy('fieldname','desc')->with('modelfunction')->get();

Answer 12

or try pluck in laravel here

DB::table('user')                 
          ->select('id','name')
          ->whereNotIn('id', DB::table('curses')->where('id_user', '=', $id)->pluck('user_id'))
          ->get();  

Answer 13

YourModel::select('column_name')->whereNotIn('book_price', [100,200])->get();