Laravel 9.* belongsToMany with belongsToMany

Question

I have DB structure:

user
    user_id
    
office
    office_id

project
    project_id

role
    role_id

permission
    permission_id
    
role_permission
    role_id
    permission_id
    
office_user
    office_user_id
    office_id
    user_id
    role_id

project_user
    project_user_id
    project_id
    user_id
    role_id

And models:

User, Project, Office, Role, Permission

Which is the right way to access to Permission model from User model? I tried this, but in officeUserPermissions() wrong role_id value, like in projectUserPermissions()

class User extends Authenticatable
{
    use HasApiTokens, HasFactory, Notifiable;

    public function projects()
    {
        return $this->belongsToMany(Project::class, 'project_user', 'user_id');
    }

    public function offices()
    {
        return $this->belongsToMany(Office::class, 'office_user', 'user_id');
    }

    public function officeRole()
    {
        return $this->belongsToMany(Role::class, 'office_user');
    }

    public function projectRole()
    {
        return $this->belongsToMany(Role::class, 'project_user');
    }

    public function officeUserPermissions()
    {
        return $this->belongsToMany(Permission::class, 'role_permission', 'role_id');
    }

    public function projectUserPermissions()
    {
        return $this->belongsToMany(Permission::class, 'role_permission', 'role_id');
    }


}

Answer 1

User and Role model have many to many relationships. You can easily access to role. But the relationship between user and permission is a bit more complicated you need to define a custom SQL query or do like this:

//role model

public function permissions() { return $this->belongsToMany(Permission::class); }

// user model
public function officeUserPermissions()
 { return $this->officeRole->map(function($role)
  { return $role->permissions}) ;
 } 

Or

$user->offlineRole()->with('permissions')