Complex User, Group and Role Relationship

[nilskretschmer]

Hi,

I use this awesome package together with https://github.com/staudenmeir/laravel-merged-relations.

I have quiet a complex scenario:

User-model:

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

    public function groups(): BelongsToMany
    {
        return $this->belongsToMany(Group::class)->using(GroupUser::class);
    }
    
        public function directPermissions(): BelongsToMany
    {
        return $this->belongsToMany(Permission::class)->using(PermissionUser::class);
    }

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

    public function permissions()
    {
        return $this->mergedRelationWithModel(Permission::class, 'all_user_permissions');
    }

Role-Model:

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

    public function groups(): BelongsToMany
    {
        return $this->belongsToMany(Group::class)->using(GroupRole::class);
    }

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

Permission-Model:

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

    public function roleUsers(): HasManyDeep
    {
        return $this->hasManyDeepFromRelations($this->roles(), (new Role())->users());
    }

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

Group-Model:

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

    public function directPermissions(): BelongsToMany
    {
        return $this->belongsToMany(Permission::class)->using(GroupPermission::class);
    }

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

    public function permissions()
    {
        return $this->mergedRelationWithModel(Permission::class, 'all_group_permissions');
    }

create_all_user_permissions_view:

    public function up(): void
    {
        Schema::createMergeView(
            'all_user_permissions',
            [(new User)->directPermissions(), (new User)->rolePermissions()]
        );
    }

Now I want to achieve the following:
I need a view (mergedRelationship) between Users and their Permissions including the Permissions he indirectly gets through the membership to a group (which itself can have direct permissions and permissions through a role which would also require a view/mergedRelation).

Is that possible and how could I accomplish that? I am not sure if I can merge multiple views in one view?

Greetings and thanks so much for these awesome packages!

[nilskretschmer]

I tried to update the existing view with this migration:

    public function up(): void
    {
        Schema::createOrReplaceMergeView(
            'all_user_permissions',
            [(new User)->directPermissions(), (new User)->rolePermissions(), (new User)->groupPermissions()]
        );
    }

But this runs into an SQL error:

  2024_05_17_120400_replace_all_user_permissions_view ..................................................................................... 7ms FAIL

   Illuminate\Database\QueryException 

  SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'laravel_foreign_key' (Connection: mysql, SQL: create or replace view `all_user_permissions` as (select `permissions`.`id`, `permissions`.`name`, `permissions`.`model_id`, `permissions`.`model_type`, `permissions`.`created_at`, `permissions`.`updated_at`, `permissions`.`destination_id`, null as `laravel_foreign_key`, null as `laravel_model`, null as `laravel_placeholders`, null as `laravel_with`, `permission_user`.`user_id` as laravel_foreign_key, 'App\\Models\\Permission' as laravel_model, 'laravel_foreign_key,laravel_model,laravel_placeholders,laravel_with' as laravel_placeholders, '' as laravel_with from `permissions` inner join `permission_user` on `permissions`.`id` = `permission_user`.`permission_id`) union all (select `permissions`.`id`, `permissions`.`name`, `permissions`.`model_id`, `permissions`.`model_type`, `permissions`.`created_at`, `permissions`.`updated_at`, `permissions`.`destination_id`, null as `laravel_foreign_key`, null as `laravel_model`, null as `laravel_placeholders`, null as `laravel_with`, `all_user_roles`.`laravel_foreign_key` as laravel_foreign_key, 'App\\Models\\Permission' as laravel_model, 'laravel_foreign_key,laravel_model,laravel_placeholders,laravel_with' as laravel_placeholders, '' as laravel_with from `permissions` inner join `permission_role` on `permission_role`.`permission_id` = `permissions`.`id` inner join `all_user_roles` on `all_user_roles`.`id` = `permission_role`.`role_id`) union all (select `all_group_permissions`.`id`, `all_group_permissions`.`name`, `all_group_permissions`.`model_id`, `all_group_permissions`.`model_type`, `all_group_permissions`.`created_at`, `all_group_permissions`.`updated_at`, `all_group_permissions`.`destination_id`, `all_group_permissions`.`laravel_foreign_key`, `all_group_permissions`.`laravel_model`, `all_group_permissions`.`laravel_placeholders`, `all_group_permissions`.`laravel_with`, `group_user`.`user_id` as laravel_foreign_key, 'App\\Models\\Permission' as laravel_model, '' as laravel_placeholders, '' as laravel_with from `all_group_permissions` inner join `groups` on `groups`.`id` = `all_group_permissions`.`laravel_foreign_key` inner join `group_user` on `group_user`.`group_id` = `groups`.`id`))

This is how I updated my User-Model before running this migration:

public function roles()
    {
        return $this->mergedRelationWithModel(Permission::class, 'all_user_roles');
    }

    public function directRoles(): BelongsToMany
    {
        return $this->belongsToMany(Role::class)->using(RoleUser::class);
    }

    public function groupRoles(): HasManyDeep
    {
        return $this->hasManyDeepFromRelations($this->groups(), (new Group())->roles());
    }

    public function groups(): BelongsToMany
    {
        return $this->belongsToMany(Group::class)->using(GroupUser::class);
    }
    public function directPermissions(): BelongsToMany
    {
        return $this->belongsToMany(Permission::class)->using(PermissionUser::class);
    }

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

    public function groupPermissions(): HasManyDeep
    {
        return $this->hasManyDeepFromRelations($this->groups(), (new Group())->permissions());
    }

    public function permissions()
    {
        return $this->mergedRelationWithModel(Permission::class, 'all_user_permissions');
    }

Can you help me out on this @staudenmeir ?

Thanks in advance!

[staudenmeir]

Hi @nilskretschmer,
It's not possible to nest merged relationships like this, but you should be able to achieve your goal with a different approach.

Replace (new User)->rolePermissions() and (new User)->groupPermissions() with their "original" relationships. So replace (new User)->rolePermissions() with two deep relationships:

• User -> Role -> Permission
• User -> Role -> Group -> Permission

In the end, the Schema::createOrReplaceMergeView() call should have five relationships. Do you know what I mean?

[nilskretschmer]

Hi,
yes I think I understand. I already tried something similar using eloquent queries on the original relationships but had some query problems left.

But I think using that approach I still have problems to merge a view that contains all user permissions - either through group or directly.

So if I get it right I need to re-built my relationships in my user model so that it is linked directly to a permission/role through a group instead of using the view of the group right?

[staudenmeir]

But I think using that approach I still have problems to merge a view that contains all user permissions - either through group or directly.

What problems exactly?

So if I get it right I need to re-built my relationships in my user model so that it is linked directly to a permission/role through a group instead of using the view of the group right?

Yes. The deep relationships you use to create a merge view can't consist merged relationships.

[nilskretschmer]

What problems exactly?

I need the view only to check permissions on a user (regardless if he got them through the group, a group role or directly). So instead of defining a view I tried to resolve this check using all relationships the user already defines. But this involves some complex queries like this:

    public function hasPermission(string $action, ?Model $model = null, ?Destination $destination = null): bool
    {
        // check if the user has the permission for the given action, model and destination
        // either through direct permissions, role permissions or group permissions
        if ($model && $destination) {
            logger()->debug('Checking permission for model and destination');
            $baseQuery = function ($query) use ($action, $model, $destination) {
                return $query->where('permissions.name', $action)
                    ->where('permissions.model_type', $model::class)
                    ->where('permissions.model_id', $model->id)
                    ->where('permissions.destination_id', $destination->id);
            };
        
            // Apply the base query to each of the permission types
            $directPermissionsQuery = $baseQuery($this->directPermissions());
            $rolePermissionsQuery = $baseQuery($this->rolePermissions());
            $groupPermissionsQuery = function ($query) use ($action, $model, $destination) {
                return $query->where('all_group_permissions.name', $action)
                    ->where('all_group_permissions.model_type', $model::class)
                    ->where('all_group_permissions.model_id', $model->id)
                    ->where('all_group_permissions.destination_id', $destination->id);
            };
        
            // Combine the queries using union
            $combinedQuery = $directPermissionsQuery->union($rolePermissionsQuery)->union($groupPermissionsQuery);
        
            // Check if any results exist
            return $combinedQuery->exists();
        }

So I think a view would be beneficial here and I am going to try to change the User's relationships to directly connect to their Group's relationships and create/update the view to use the relationships instead of the group's view.

[nilskretschmer]

Hey @staudenmeir
using HasManyDeep-relations in the User model to link to group permissions and roles and then updating the existing view worked without problems! Thank you again.