Laravel 6 WhereIn and WhereNotIn query builder with example

In this tutorial, we will learn Laravel whereIn and whereNotIn subquery query builder with an example. we will also dump the Laravel query to MySQL raw query. All you have to do is to pass an array within wherein and wherenotin method. Below is the example of whereIn and whereNotIn.

Table of Contents

Note: To dump/check or convert eloquent query to MySQL raw query. You can use the following code.

DB::enableQueryLog();
// your eloquent query
return DB::getQueryLog();

WhereIn query

WhereIn method will give you all the results. all you have to do is to pass an array. Let's assume you want to get the user with id 1, 2, 3 and 5 from the user table. Below is the eloquent query.

User::whereIn('id', [1, 2, 3, 5])->get();

If you dump the query you will get the following MySQL query.

select * from users where id in (1, 2, 3, 5);

Sometimes you may wish to nest the whereIn query. Below is the example of the nested query.

$user = User::whereIn('id', function($query) {
    $query->select('user_id')->from('role_user');
})->get();

The above query will give you all the user detail which is mapped/available in role_user table and the above query will output the below MySql raw query.

SELECT * FROM `users` WHERE `id` IN (
    SELECT `user_id` FROM `role_user`
)

WhereNotIn query

WhereNotIn method is similar to whereIn method. If you want the exclude the id 1, 2, 3 and 5 from the user table. then you can use the following whereNotIn eloquent query.

User::whereNotIn('id', [1, 2, 3, 5])->get();

The above eloquent query will give you the following MySql raw query.

select * from users where id not in (1, 2, 3, 5);

Sometimes you may wish to exclude all the user who is not is role_user table. Below is the example to exclude the users using Laravel whereNotIn eloquent query.

$user = User::whereNotIn('id', function($query) {
    $query->select('user_id')->from('role_user');
})->get();

The above eloquent query will give you the following MySql query.

SELECT * FROM `users`  WHERE `id` NOT IN (
    SELECT `user_id` FROM `role_user`
)