Spread the love

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)

Leave a Reply