Laravel whereExists and whereNotExists Query Example

In this laravel tutorial, you will learn how to use whereExists and whereNotExists eloquent methods with eloquent query builder and model.

Let you develop a job application in laravel. Where an employer can post a job and hire employees.

When an employer posts a job. And he wants someone to hire only every manager or web developer. Then the problem comes. Because you have registered users of different designations in you.

At this time you can use whereExist and whereNotExist method with eloquent models.

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

To demonstrate whereExists() method using the above tables:

If you are employer and want to hire a specific position employee. So you 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.

The problem with this, if you have a lot of users registered in our job application, you don’t want to get them all and to make sure that only those job applicants who are Banker will receive the email.

So you can write laravel whereExist query as follow:

$users = User::whereExists(
               function($query) use($job) {  
                 $query->from('users_position')
                       ->where('position_id', $job->position_id);
               })->get();        
return $users;

This query all from the users table, and used whereExist clause and use a closure argument.

Laravel whereNotExists Using Eloquent Model Example

To demonstrate whereNotExists() method using the above tables:

When you post a new job and want to hire employees. But you want to send notification of this new job opportunity for some designation users and want to leave some designation users.

The problem with this, if you have a lot of users registered in our job application, you don’t want to get them all and to make sure that only those job applicants who are Not Banker, Designer, etc will receive the email.

So you can write laravel whereNotExist query as follow:

$users = User::whereNotExists(
               function($query) use($job) {
                 $query->from('users_position')
                       ->where('position_id', $job->position_id);
               })->get();
return $users;

This query all from the users table, and used whereNotExist clause and use a closure argument.

Conclusion

In this laravel tutorial, you have learned whereExists and whereNotExist method in eloquent models.