How To Create A Subquery With WhereIn And WhereNotIn Clauses In Laravel

By:
On:
In: Laravel
Tagged: , , ,
With: 0 Comments

Hello there Geek,

As you may be aware, Laravel is a popular PHP framework today. As a result, every point will be required for database-related and important purposes. In this post, I’ll show you how to create a subquery with whereIn and whereNotIn clauses from scratch.

In Laravel 6, 7, 8, and 9, we may need to use a select query with a where in or where not in clause on occasion. We can easily create a query in MySQL. However, we are unsure how to apply it to the Laravel query builder. However, in this example, we will learn how to convert a MySQL query into a Laravel query builder with a 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 the first query, we basically need users from the invite users table. We can normally retrieve using the SQL query below:

SQL Query:

SELECT * FROM `users` 

	WHERE `id` IN (

		SELECT `user_id` FROM `invite_users`

		)

We can convert for a Laravel application as shown below.

Laravel Query:

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

Where Not In Query

In the second query, we basically want users who are not in the invite users table. We can normally retrieve using the SQL query below:

SQL Query:

SELECT * FROM `users` 

	WHERE `id` NOT IN (

		SELECT `user_id` FROM `invite_users`

		)

We can convert for a Laravel application as shown below.

Laravel Query:

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

All the best nerd!

Leave a Reply