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

Greetings, I'm Devendra Dode, a full-stack developer, entrepreneur, and the proud owner of Tutsmake.com. My passion lies in crafting informative tutorials and offering valuable tips to assist fellow developers on their coding journey. Within my content, I cover a spectrum of technologies, including 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. Whether you're starting out or looking for advanced examples, I provide step-by-step guides and practical demonstrations to make your learning experience seamless. Let's explore the diverse realms of coding together.

Leave a Reply

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