Many to many with 3 tables

[micobarac]

I have the following database tables:

users

id
username

companies

id
name

roles

id
name

permissions

id
name

user_company_roles

user_id
company_id
role_id

role_permissions

role_id
permission_id

Can I use your library and how to perform the following queries:

• get user companies
• get user roles by a company
• check if user has a role in any company (check for super admin)
• check if user has a role in a company
• check if user has a permission in a company

I have another issue:

I cannot perform wherePivot on withPivot as wherePivot returns RetrievesIntermediateTables instead of InteractsWithPivotTable.

And another one:

When I use hasManyDeepFromRelations, filtering related relation by with clause doesn't have any effect on a new relation. It still returns all related rows.

Any help would be appreciated. :)

[staudenmeir]

get user companies get user roles by a company check if user has a role in any company (check for super admin) check if user has a role in a company

You can implement these with native BelongsToMany relationships. Have you looked in that?

check if user has a permission in a company

Let's complete the other tasks first.

I cannot perform wherePivot on withPivot as wherePivot returns RetrievesIntermediateTables instead of InteractsWithPivotTable.

What do your relationship and query look like?

When I use hasManyDeepFromRelations, filtering related relation by with clause doesn't have any effect on a new relation. It still returns all related rows.

What do your relationship(s) and query look like?

[micobarac]

class User extends Authenticatable implements JWTSubject
{
    use Notifiable, HasRelationships;

    /**
     * A user may be given various company roles.
     */
    public function companies(): BelongsToMany
    {
        return $this->belongsToMany(Company::class, 'user_company_roles')
            ->as('positions')
            ->withPivot('role_id')
            ->withTimestamps();
    }

    /**
     * A user may be given various company roles.
     */
    public function roles(): BelongsToMany
    {
        return $this->belongsToMany(Role::class, 'user_company_roles')
            ->as('positions')
            ->withPivot('company_id')
            ->withTimestamps();
    }

    /**
     * A user may be given various company permissions.
     */
    public function permissions(): HasManyDeep
    {
        return $this->hasManyDeepFromRelations($this->roles(), (new Role)->permissions());
    }
}

class AuthController extends Controller
{
    /**
     * Get the Companies.
     *
     * @return \Illuminate\Http\JsonResponse
     */
    public function companies()
    {
        return response()->json(auth()->user()->companies()->orderBy('slug')->get()->makeHidden('positions')->unique());
    }

    /**
     * Get the Roles.
     *
     * @return \Illuminate\Http\JsonResponse
     */
    public function roles()
    {
        return response()->json(auth()->user()->roles()->wherePivot('company_id', 1)->orderBy('slug')->get()->makeHidden('positions'));
    }

    /**
     * Get the Permissions.
     *
     * @return \Illuminate\Http\JsonResponse
     */
    public function permissions()
    {
        return response()->json(auth()->user()->permissions()->where('company_id', 2)->orderBy('slug')->get()->makeHidden('laravel_through_key')->unique());
    }
}

[micobarac]

This is just a test code.

With permissions query, wherePivot does not work, for the reason I mentioned above, but where seems to be working ok.

[staudenmeir]

The package doesn't provide a wherePivot() method because it couldn't provide a benefit over using where().

When I use hasManyDeepFromRelations, filtering related relation by with clause doesn't have any effect on a new relation. It still returns all related rows.

How does the filter look like?

[micobarac]

    public function permissions(): HasManyDeep
    {
        return $this->hasManyDeepFromRelations($this->roles()->wherePivot('company_id', 1), (new Role)->permissions());
    }

[staudenmeir]

This is a limitation of hasManyDeepFromRelations(). You need to add the constraint to the combined relationship.