In this tutorial you will learn about the Laravel whereExists and whereNotExists Query Example and its application with practical example.
In this Laravel whereExists and whereNotExists Query Example tutorial, I will show how to use whereExists and whereNotExists methods with eloquent in laravel query builder and laravel model.
Laravel whereExists and whereNotExists Query Example
Suppose you are developing a job portal in laravel. In this portal an employer can post a job and hire employees as per the require positions. When an employer posts a job and they wants to hire only manager or web developer. In this case we have to face problem because you have registered users of different designations.
Suppose you have the following tables into your job application:
positions
table
id | title | timestamps
users
table
id | name | email | timestamps
users_position
table (a pivot table for user and position)
id | user_id | position_id | description | timestamps
jobs
table
id | position_id | name | description | timestamps
Laravel whereExists Using Eloquent Model Example
In this example we will demonstrate the use of whereExists() method in laravel. As per the above database tables If you are an employer and you are looking to hire for some specific position employee. Then you have posted a new job and wants to hire an employees. So you will be notified by sending an email for a new job opportunity for specific designation users.
If you have a large amount of registered users in your job application, now you don’t want to get them all and to make sure that only those job applicants who are Banker will receive the email.
Here you can use laravel whereExist query as following:
1 2 3 4 5 6 |
$users = User::whereExists( function($query) use($job) { $query->from('users_position') ->where('position_id', $job->position_id); })->get(); return $users; |
This above query will fetch all users from the users
table, and used whereExist clause and use a closure argument to apply condition.
Laravel whereNotExists Using Eloquent Model Example
In this example I’ll demonstrate the use of whereNotExists() method using the above tables as following:
As per the above database schema suppose you want to post a new job and want to hire employees. Now you want to send emails of this new job opening to specific designation users and want to skip other designation users.
Now the problem with this is you have a large amount of users registered in your job portal, now you don’t them all to make sure that only those job applicants who are Not Banker, Designer, etc will receive the email.
Now, here you can use laravel whereNotExist query as following:
1 2 3 4 5 6 |
$users = User::whereNotExists( function($query) use($job) { $query->from('users_position') ->where('position_id', $job->position_id); })->get(); return $users; |
This above query will fetch all users from the users
table, and used whereNotExist clause and use a closure argument.