Using Laravel’s Where Exists Clauses

Scenario: Assuming we have 4 tables, for the sake of simplicity, I omitted the columns of the tables. The actual tables may have more columns.

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

Then for example, an employer posted a new job and wants to hire Programmers, then those users (job applicants) who are programmers will be notified by sending them an email to let them know about the new posted job.

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

So the solution is Where Clause and usedwhereExists method which allows us to write where exists SQL clauses.

To do this, first we need to set a relationship between the user and our pivot table users_position

class User extends Model
{
public function userPosition()
{
return $this->belongsTo(UserPosition::class);
}
}

After that we can use the code below wherever we wanted to implement it. Just make sure that $job is instantiated.

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

If you gonna look at the code above, we query all from the users table, and used whereExist clause and use a closure argument.

And since we already set our relationship between users and users_position table, we have now access to the users_position table and used it to filter the users by matching the users_position.position_id and jobs.position_id.To be in line with the example, we get only those who are programmers.

Enjoy!