Spread the love

In this guide we will learn to create SQL in query using larvae l eloquent builder. Laravel eloquent provides 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 in in laravel eloquent we can use whereIn 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 whereIn query

Syntax to use:

whereIn('COLUMN_NAME', Array);

//OR

whereIn('COLUMN_NAME',Function);

SQL of above query will be

select * from `table` where  `COLUMN_NAME` in  ('Array')

//OR

select * from `table` where  `COLUMN_NAME` 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 with simple examples

Example 1 – whereIn with array example

In this example i will show you simple whereIn 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("*")
                        ->whereIn("id", [1,2,3])
                        ->get();
      \\or
      DB::table('articles')->whereIn("id",[1,2,3])->get();

   }
}

Output will be :

select * from `articles` where `id` in (1,2,3) 

Example 2 – whereIn 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())
                              ->whereIn('category_id', ['223', '15'])
                              ->where('active', 1);
                         })
                        ->get();
      \\or
       DB::table('articles')
       ->whereIn('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` in (select article_type_id from article_categories where category_id in (223,15)  )

Example 3 – Multiple whereIn and orWhere condition

In this example we will use whereIn, 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->whereIn("cat_id",[2,4,6])->orWhere("status",1);
                        })
                        ->get();
   }
}

Output

select * from `articles` where `id` in (1,2,3) and (`cat_id` in (2,4,6) or `status` = 1)

Example 4 – Complicated Raw query in where

In this example we will use DB::raw to use complicated query in our laravel application. Sometimes we wanted to use aggregate function in laravel where so

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\User;
use DB;
  
class ArticleController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
      $Article = Article::select("*")
                        ->whereIn("id",\DB::raw("select id from table"))
                         ->get();

     //
    $Article = Article::select("*")
                        ->where(\DB::raw(" in (select id from table)"))
                         ->get(); 
   }
}

Output

select * from `articles` where `id` in (select id from table) 

Leave a Reply