Are you struggling with Laravel’s whereIn query? In this comprehensive guide, you’ll learn how to harness the power of Laravel’s whereIn Eloquent builder for creating dynamic queries, subqueries, and more. Explore the multiple ways to use this versatile method and gain a deep understanding with practical examples.
Below examples will work with any version of laravel 5 whereIn, laravel 6 whereIn, laravel 7 whereIn, laravel 8 whereIn, laravel 9 whereIn and laravel 10 whereIn.
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.
Syntax to use:
whereIn('COLUMN_NAME', Array);
//OR
whereIn('COLUMN_NAME',Function);The SQL equivalent of the 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. Now, let’s dive into some practical examples:
Example 1 – whereIn with array example
In this example, we’ll demonstrate a simple whereIn query with an array condition using 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’ll use a closure function to create a 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’ll combine whereIn, orWhere, and subquery for more complex conditions
<?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) 
