CODEWITHSUNDEEP
laravel-4eloquent
kristian nissen
kristian nissen

Reputation: 2907

Laravel 4.2 Eloquent fetch specific user and specific role

In the Laravel Eloquent many to many example the scenario of users and roles is being used http://laravel.com/docs/eloquent#many-to-many say I have a group of users with the same family "Simpson" name and I want to get all of them with the role of being "Engineer". How can I accomplish this using Eloquent? In the example the "has" method is used http://laravel.com/docs/eloquent#querying-relations but how do I go about querying the database for a value present in both the user table and the role table?

I am not looking for the

User::find(1)->roles();

I am looking for a way to do something like

User::whereFamilyName('simpson')->roles()->whereName('Engineer')

the 2nd whereName would the be querying the roles table and not the user table.

Upvotes: 0

Views: 540

Answers (2)

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81187

If you need to get users that have particular role:

$role = 'Engineer';

// users with family name Simpson having role Engineer
$users = User::whereFamilyName('Simpson')
      ->whereHas('roles', function ($q) use ($role) {
           $q->where('roles.name', $role); // it joins the table so you may need to use prefixed column name, otherwise you can still use dynamic whereName()
      })
      // with('roles') if needed
      ->get();
// returns Collection of User models without roles or with all roles attached to each user if needed

Otherwise simply join the tables:

User::whereFamilyName('Simpson')
   ->join('role_user', 'role_user.user_id', '=', 'users.id')
   ->join('roles', 'role_user.role_id', '=', 'roles.id')
   ->whereName('Engineer') // again columns name conflict may occur
   ->get(['users.*']);

All values hardcoded here to make it clear.

Upvotes: 2

The Alpha
The Alpha

Reputation: 146269

You may try this:

$users = User::with(array('roles' => function($query){
    $query->whereName('Engineer');
}))->whereFamilyName('simpson')->get(); // whereFamilyName = family_name in DB

Hope you have declared the relationship properly and also make sure whether it's roles or role in the User model for relationship with roles table. I've used roles because you have used it in your question's code.

Upvotes: 0

Related Questions