In this guide we will learn to create SQL not in query using laravel whereIn eloquent builder. whereNotIn laravel can used multiple ways to build the query one the of the feature of laravel eloquent is creating dynamic query based on condition and sub queries too.
To create where not in in laravel eloquent we can use whereNotIn method which accepts two parameter one is column name and other one is list of Array.
Here is the simple syntax to use the laravel whereNotIn query
Syntax to use:
whereNotIn('COLUMN_NAME', Array);
//OR
whereNotIn('COLUMN_NAME',Function);SQL of above query will be
select * from `table` where `COLUMN_NAME` not in ('Array')
//OR
select * from `table` where `COLUMN_NAME` not in (select id from `table2')As you can see i have written multiple way to use same method with different type of parameters. So lets begin tutorial with example
Example 1 – whereNotIn with array example
In this example i will show you simple whereNotIn with array condition with two parameters
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\User;
class ArticleController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
$Article = Article::select("*")
->whereNotIn("id", [1,2,3])
->get();
\\or
DB::table('articles')->whereNotIn("id",[1,2,3])->get();
}
}Output will be :
select * from `articles` where `id` not in (1,2,3) Example 2 – whereNotIn with subquery using closure function
In this example we will use closure function to create subquery.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\User;
class ArticleController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
$Article = Article::select("*")
->where("category_id", function($query){
$query->select('article_type_id')
->from(with(new ArticleCategory)->getTable())
->whereNotIn('category_id', ['223', '15'])
->where('active', 1);
})
->get();
\\or
DB::table('articles')
->whereNotIn('id', function($query)
{
return $query->select('article_type_id')
->from('article_categories')
->whereIn('category_id', ['223', '15']);
})->get();
}
}Output
select * from `articles` where `category_id` not in (select article_type_id from article_categories where category_id in (223,15) )Example 3 – Multiple whereNotIn and orWhere condition
In this example we will use whereNotIn, orWhere and subquery.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\User;
class ArticleController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
$Article = Article::select("*")
->whereIn("id",[1,2,3])
->where(function($query){
$query->whereNotIn("cat_id",[2,4,6])->orWhere("status",1);
})
->get();
}
}Output
select * from `articles` where `id` not in (1,2,3) and (`cat_id` in (2,4,6) or `status` = 1)