In this tutorial you will learn about the Laravel whereIn, whereNotIn With SubQuery Example and its application with practical example.
In this Laravel whereIn and whereNotIn with subquery example article, I’ll show you how to use whereIn and whereNotIn subquery in laravel queries.
Laravel SubQuery
In laravel, sometimes you may want fetch records from multiple database tables having complex and nested relationships. You may also require to exclude some nested data from DB table. So this can be achieved using whereIn and WhereNotIn clause in subquery.
Laravel WhereIn SubQuery
In the given example query we will fetch data from user table, which is available in the user_role table using whereIn subquery.
WhereIn SubQuery Using Query Builder
|
1 2 3 |
DB::table('users')->whereIn('id', function($query) { $query->select('user_id')->from('role_user'); })->get(); |
WhereIn SubQuery Using Model
|
1 2 3 |
User::whereIn('id', function($query) { $query->select('user_id')->from('role_user'); })->get(); |
The above laravel statement given whereIn subQueries represents following SQL query:
|
1 2 3 |
SELECT * FROM `users` WHERE `id` IN ( SELECT `user_id` FROM `role_user` ) |
Laravel WhereNotIn SubQuery
In the given example query fetch data from user table, which is not available in the user_role table by using whereNotIn subquery.
whereNotIn SubQuery Using Query Builder
|
1 2 3 |
DB::table('users')->whereNotIn('id', function($query) { $query->select('user_id')->from('role_user'); })->get(); |
WhereNotIn SubQuery Using Model
|
1 2 3 |
User::whereNotIn('id', function($query) { $query->select('user_id')->from('role_user'); })->get(); |
The above laravel statement given whereNotIn subQueries represents following SQL query:
|
1 2 3 |
SELECT * FROM `users` WHERE `id` NOT IN ( SELECT `user_id` FROM `role_user` ) |
