CODEWITHSUNDEEP
phpmysqllaraveljoin
Pritika
Pritika

Reputation: 306

Add condition and joins to connected tables in laravel

I have a user table and it has userRole. I want to add condition that fetch all the users having "role_id" 1 that is in userRole.

Relationship in user Model:

public function userRole() 
{   
  return $this->hasOne(UserRole::class); 
}

And controller code:

$users = User::with(["userRole" => function ($query){
                        $query->where('role_id', 1);   
                    }])->get();

This is not working fine. Its fetching all the users and fetching userRole in case if its role_id is 2 otherwise userRole is empty.

Upvotes: 1

Views: 83

Answers (4)

Mihir Bhende
Mihir Bhende

Reputation: 9055

Explanation :

with() is used to eager load. So basically using with('userRole') will get you all roles for that particular user.

When you use :

User::with(["userRole" => function ($query){
     return $query->where('role_id', 1);   
}])->get();

You are telling Laravel, give me all users, and their roles but roles fetched should only have role_id of 1. Do not select roles other than this condition.

Laravel has a function whereHas which does not select anything but filters the parent relationship based on on condition of the child.

For example. we need to get users having role id of 1 : 

User::wherehas('userRole', function ($q) {
    return $q->where('role_id','1');
})->get();

Now this will tell Laravel to get me all users who have atleast 1 entry userRoles with role_id of 1.

This will just filter Users based on relation of roles. However, we need to separately add with() clause to get the roles. 

User::with('userRole')->wherehas('userRole', function ($q) {
    return $q->where('role_id','1');
})->get();

Upvotes: 0

Ankur Khurana
Ankur Khurana

Reputation: 34

This may help you. Please try adding the below snippet in your controller

User::with(['userRole'])->wherehas('userRole', function ($query) {$query->where('role_id','1');})->get();

Upvotes: 1

Pritika
Pritika

Reputation: 306

Got the solution we can do this by wherehas

User::with(['userRole'])->wherehas('userRole', function ($query) {$query->where('role_id','1');})->get();

Upvotes: 0

Morteza
Morteza

Reputation: 73

Try loading reverse of relation. Something like this:

// in UserRole.php file
public function users() 
{   
  return $this->hasMany(User::class); 
}

and your query like so:

$role = UserRole::with("users")->where('id', 2)->get();
$users = $role->users;

Upvotes: 0

Related Questions