Mutli relationships with Eloquent and Laravel

Question

I have 6 tables:

permissions:
id
permission_name

roles:
id
role_name

user_roles:
id 
user_id 
role_id

user_permissions:
id 
user_id 
permission_id

role_permissions:
id
role_id
permission_id

users:
id
username
email

Now I like to add a custom function into my Eloquent model for permissions named getUsersWithPermission().

Now it is possible that user has the permission by the role he has given (user table join on user_roles, join that table on role_permissions and last that table join on permissions.

Or the user has a specific permission set in the user_permissions table (join user_permissions table on user table and join the permissions table on the user_permissions table)

How can I create a Eloquent model function that returns all the users with a specific permission say: "notification.error.process" where that is the permission name

A hasMany doesn't quite do what I want (and I cannot define multi tables in there). I am using Laravel 5.6.

Answer 1

If you defined your many-to-many relations correctly (User-Permission and User-Role-Permission), the following should do the trick:

$permissionName = 'notification.error.process';
$usersWithPermission = User::whereHas('permissions', function($query) use ($permissionName) {
  $query->whereName($permissionName);
})->orWhereHas('roles.permissions', function($query) use ($permissionName) {
  $query->whereName($permissionName);
})->get();

Answer 2

Try to understand your table structure using this ER diagram.

TO handle this situation, You may create 3 'Eloquent Models'

1. User
2. Role
3. Permission

in every model you may create belongsToMany() relationship to each other models.

one example, On 'User Model'

public function permissions()
{
    // need to define any other differences in your tables foreign keys as  2nd, 3rd parameters and so on.
    return $this->belongsToMany(Permission::class);
}

Now you need to get all the users 'whom has a role which has the privilege' OR 'whom directly has the privilege.

to do so.

$users = User::whereHas('privileges', function($query) {
    $query->where('permission_name', 'notification.error.process');
})->orWhereHas('roles.permissions', function($query) {
    $query->where('permission_name', 'notification.error.process');
})->get();

I hope you will get an idea. your code may be slightly different from mine. but this is the concept.