In this guide we will learn to create SQL in query using laravel whereIn eloquent builder. whereIn 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 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 laravel wherein query with example
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)