Laravel Eloquent "WHERE NOT IN"
LaravelLaravel 4EloquentLaravel Problem Overview
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.
Laravel Solutions
Solution 1 - Laravel
Query Builder:
DB::table(..)->select(..)->whereNotIn('book_price', [100,200])->get();
Eloquent:
SomeModel::select(..)->whereNotIn('book_price', [100,200])->get();
Solution 2 - Laravel
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
Solution 3 - Laravel
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();
Solution 4 - Laravel
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();
Solution 5 - Laravel
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();
Solution 6 - Laravel
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();
Solution 7 - Laravel
You can use WhereNotIn in the following way:
$category=DB::table('category')
->whereNotIn('category_id',[14 ,15])
->get();`enter code here`
Solution 8 - Laravel
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]);
Solution 9 - Laravel
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.
Solution 10 - Laravel
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();
Solution 11 - Laravel
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();
Solution 12 - Laravel
$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();