Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Laravel Eloquent "WHERE NOT IN"

People also ask

How to use WhereNot in laravel?

If you need to use sql wherenotin query in laravel then you can use with array. Laravel provide wherenotin() to use sql wherenotin query. in wherenotin() we just need to pass two argument one is column name and another if array of ids or anything that you want.

Where IS NOT NULL in laravel?

Check if not null: whereNotNullSELECT * FROM users WHERE last_name IS NOT NULL; The equivalent to the IS NOT NULL condition in Laravel Eloquent is the whereNotNull method, which allows you to verify if a specific column's value is not NULL .

How do you pluck in laravel?

$users = User::all()->pluck('username'); You can also use pluck() method on nested objects like relationships with dot notation. Laravel Pluck method can be very useful when you extract certain column values without loading all the columns. You can benefit from the Laravel pluck method in the blade views as well.


Query Builder:

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

Eloquent:

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

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


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

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

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

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