Laravel – whereIn and whereNotIn with subquery example using query builder

25 Tháng năm, 2023 Laravel Eloquent Query 0 Comments 133 Views

25 Th52023

As you know laravel is very popular php framework in today. So every point will be require for database related and that important. In this post i would like to share with you how to use subquery with whereIn and whereNotIn clause from scratch.

We may sometimes require to use select query with where in or where not in clause in laravel 6, laravel 7, laravel 8, laravel 9 and laravel 10. we can simply make query using MySQL. But we don’t know how to use it on laravel query builder. However, In this example we will learn how to convert mysql query into laravel query builder of wherein with subquery. This example is from scratch, so i will give you first mysql query and then we will convert it into laravel query builder.

In this example i have to tables as listed bellow:

1) users

2) invite_users

Where In Query

In first query, basically we require users that only in invite_users table. we can normally retrieve using bellow sql query:

SQL Query:

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

As bellow we can convert for laravel application.

Laravel Query:

$users = DB::table("users")->select('*')
            ->whereIn('id',function($query){
               $query->select('user_id')->from('invite_users');
            })
            ->get();

Where Not In Query

In second query, basically we require users that not in invite_users table. we can normally retrieve using bellow sql query:

SQL Query:

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

As bellow we can convert for laravel application.

Laravel Query:

$users = DB::table("users")->select('*')
            ->whereNOTIn('id',function($query){
               $query->select('user_id')->from('invite_users');
            })
            ->get();

I hope it can help you….