Laravel whereIn, whereNotIn With SubQuery Example

Laravel whereIn, whereNotIn With SubQuery Example

Laravel whereIn and whereNotIn with subquery example. In this tutorial, you will learn how to use whereIn and whereNotIn subquery in laravel.

Sometimes you want to get nested data from database tables and some time exclude some nested data from DB table. So you can use whereIn and WhereNotIn subquery for that.

Laravel WhereIn SubQuery

The following examples queries fetch data from user table, which is available in the user_role table using whereIn subquery.

See the following examples:

Example 1: WhereIn SubQuery Using Query Builder

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

Example 2: WhereIn SubQuery Using Model

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

When you dump the above given whereIn subQueries you will get the following SQL query:

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

Laravel WhereNotIn SubQuery

The following examples queries fetch data from user table, which is not available in the user_role table by using whereNotIn subquery.

See the following examples:

Example 1: whereNotIn SubQuery Using Query Builder:

DB::table('users')->whereNotIn('id', function($query) {
    $query->select('user_id')->from('role_user');
})->get();

Example 2: WhereNotIn SubQuery Using Model

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

When you dump the above given whereIn subQueries you will get the following SQL query:

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

Conclusion

In this laravel where in and where not in subquery example tutorial, you have learned how to implement subquery using laravel whereIn and wherenotin eloquent methods in laravel.

Recommended Laravel Posts

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

Your email address will not be published. Required fields are marked *