Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

tylernathanreed's avatar

Chaining Multiple Belongs To Many Relationships

Consider the following Schema:

users
    id
    ...

roles
    id
    ...

permissions
    id
    ...

role_user
    role_id
    user_id
    ...

permission_role
    permission_id
    role_id
    ...

And the following Relationships:

User.php

public function roles()
{
    return $this->belongsToMany(Role::class);
}

Role.php

public function users()
{
    return $this->belongsToMany(User::class);
}

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

Permission.php

public function roles()
{
    return $this->belongsToMany(Role::class);
}

Now then, let's say that I want to find what Permissions a User has:

User.php

public function permissions()
{
    return ???;
}

Or perhaps I want to look up the Users from the Permission:

Permission.php

public function users()
{
    return ???;
}

How can this functions be completed? For the relational functions, I want to be able to call both $permission->users() and $permission->users.

I know how to do Query Scopes for these types of Relationships (albeit complicated):

User.php

public function scopeWithPermission($query, Permission $permission)
{
    $query->join('role_user', 'users.id', '=', 'role_user.role_id') // Join with `role_user`
            ->join('permission_role', 'role_user.role_id', '=', 'permission_role.role_id') // Join with `permission_role`
            ->where('permission_role.permission_id', $permission->id); // Filter by Permission ID
}

And I know how to get the results that I'm looking for, but not they're not in a Relationship, meaning I can't call both $permission->users() and $permission->users:

Permission.php

public function users()
{
    // Determine the Roles associated with this Permission
    $roles = $this->roles()->with('users');

    // Determine the Users associated with the Roles
    $users = $roles->get()->pluck('users');

    // Turn the Collection of Collections into a Single Collection
    $users = call_user_func_array('array_merge', $users->toArray());

    // Pluck the IDs from the Users
    $ids = array_column($users, 'id');

    // Remove Duplicate IDs
    $ids = array_unique($ids);

    // Return the Users associated with this Permission
    return User::whereIn('id', $ids);
}
$permission->users();

Illuminate\Database\Eloquent\Builder
$permission->users()->get();

Illuminate\Database\Eloquent\Collection {
    all: [
        App\Users {
            ...
        },
        ...
    ]
}
$permission->users;

LogicException with message 'Relationship method must return an object of type Illuminate\Database\Eloquent\Relations\Relation'

I'm game for creating a new Relation called belongsToManyThroughMany if that's what it comes to. :P

0 likes
7 replies
tylernathanreed's avatar

Nothing yet? I'm surprised. I didn't think that this would be that difficult.

bashy's avatar

Why are you doing joins and wheres for a simple relation via permissions?

tylernathanreed's avatar

@bashy, do you mean the Query Scope? I can't directly scope Permissions from the User, because I have to go through Roles. I wish I could just do something like

$user->roles()->permissions

To get all of the Permissions for a User.

And for the Query Scope, I can't really do

$query->where('permission_id', $permission->id);

without all of the joins to go from User to role_user to Roles to role_permission.

If you have a cleaner solution for any of this, I'm all ears. I really just need to bridge the gap between Users and Permissions, and be able to perform Query Scopes for Authorization. I don't want to add a permission_user table, as then I'm risking referential integrity.

bashy's avatar

@tylernathanreed I've done that in one of my sites. Do you want

$user = User::find($id);

$user->with('roles.permissions')->get();
tylernathanreed's avatar

@bashy That gave me an odd results set. I got all of the users back, and only some had roles with their permissions. What's even worse, the user I started the query on didn't even have their roles attached to the selection.

bashy's avatar

Check their relation in the db? That's a simple sub relation from roles. Using the dot notation.

Please or to participate in this conversation.