Laravel belongsToManyThrough alternatives|workarounds

Question

I'm trying to create roles for the users in a Laravel application. The database looks like this:

users table
-----------------------------------------------------
 - id
 - name
 ...

role_user table
-----------------------------------------------------
 - role_id
 - user_id

 
roles table // I need this table because I want to predefined role with abilities
-----------------------------------------------------
 - id
 - name
 ...

ability_role table
-----------------------------------------------------
 - ability_id
 - role_id

abilities table
-----------------------------------------------------
 - id
 - ability_name
 ...

I defined the relationships between the models, it's working correctly. I want to get all abilities of the auth user. What is the best way to do that?

The meaning of role table is that I want to define roles with abilities (for example: admin will have all the abilities (can viewAny product), but I want to define a productManager role, what has just update ability for a project). And after for the roles I want to able to dynamically change the abilities.

Now what I can do is get the abilities through foreaches, what I think is not an optimal way, can I get somehow this data directly from the database through eloquent? Or maybe I shouldn't strick to the eloquent, and write a raw DB query instead?

Any help would appreciate!!

Answer 1

You can achieve the same with more efficient way using Spatie Permission but anyhow if you have both the many to many relationships defined then you can do something like this to fetch the auth user abilities,

$abilities = auth()->user()->load('roles.abilities')->roles->flatMap(function ($role) {
    return $role->abilities;
})->unique('id'); 

This will load all roles for the auth user, including the abilities into a single collection of abilities.

And if there is the concern of performance, you can do joins like,

$abilities = Ability::select('abilities.*')
    ->join('ability_role', 'abilities.id', '=', 'ability_role.ability_id')
    ->join('role_user', 'ability_role.role_id', '=', 'role_user.role_id')
    ->where('role_user.user_id', auth()->id())
    ->distinct()
    ->get();