L5 Eloquent Query Question - Roles / Groups

I would give this a watch: https://laracasts.com/lessons/users-and-roles. While its not referred to as a "group" its essentially the same technique. I recently used the same process on an app with L5.

@nolros Speaking eloquent, there is no other option than:

// single group
$group->members;

// multiple groups
Member::whereHas('group', function ($q) use ($companyId) {
    $q->where('company_id', $companyId);
})->get();

One simple call and you're done, no need for anything else.

@JarekTkaczyk as always thank you very much.

Debug snapshot - let me know if you think this is optimal query. Code below.

http://www.cmosolution.com/laracasts/query.png

In member models

function group()
{
    return $this->belongsTo(EloquentSecurityGroup::class, 'security_group_id', 'id');
}

In a service class

    public function getRoles(WorkSpace $workSpace, User $user)
    {
        $result = array();
    
        // get all security groups for this workspace   
        $securityGroups = EloquentSecurityGroup::where('space_id', '=', $workSpace->id)->get();
    
        $userId = $user->uuid;  
    
        // iterate through each group to find members for each of the groups
        foreach ($securityGroups as $group)
        {
            $groupId = $group->id;
    
            /// look through each group to see if the user exists in that security group
            $result[] = EloquentSecurityGroupMember::whereHas('group', function ($q) use ($groupId, $userId)
            {
                $q->where('security_group_id', $groupId)
                    ->where('user_id', $userId);;
    
            })->get();
    
        }
    
        return $result;

    }

Thanks again, need your brain for 2 months. I promise to keep it in good condition :)

@nolros You are executing multiple queries in this loop. You don't need that. Also you get an array of collections, doesn't look convenient.

public function getRoles(WorkSpace $workSpace, User $user)
{
        // get all security groups for this workspace   
        $groupsIds = EloquentSecurityGroup::where('space_id', '=', $workSpace->id)->lists('id');
    
        $userId = $user->uuid;
    
        return EloquentSecurityGroupMember::whereHas('group', function ($q) use ($groupsIds, $userId) {
             $q->whereIn('security_group_id', $groupsIds)
               ->where('user_id', $userId);
        })->get();
}

@JarekTkaczyk once last question

Is it possible to merge the two queries below into one i.e. for $sids and $permissionMaps

The relationships are as follows:

// Group relationship to permission map 
$securityGroup->permission_level_id <code>belongsTo</code> $permissionMap->level_id

// Member relationship to group
$securityGroupMembe->security_group_id <code>belongsTo</code> $securityGroup->id

// Permission level (Role) to permission map which is object + mask + level
$permissionMap->level_id <code>belongsTo</code> $permissionLevel->id

    /**
     * EloquentSecurityGroup - group
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function members()
    {
        return $this->hasMany(EloquentSecurityGroupMember::class, 'id' ,'security_group_id');
    }

    /**
     * EloquentSecurityGroupMember - user id to group
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function group()
    {
        return $this->belongsTo(EloquentSecurityGroup::class, 'security_group_id' ,'id');
    }

    /**
     * EloquentPermissionMap - object class + permission level + mask  
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function level()
    {
        return $this->belongsTo(EloquentPermissionLevel::class, 'level_id', 'id');
    }

    /**
     * EloquentPermissionLevel - Roles
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function maps()
    {
        return $this->belongsTo(EloquentPermissionMap::class, 'id', 'level_id');
    }
    
    
    /**
     * @param   Space $space
     * @param   ObjectIdentity $object
     * @param   User $user
     * @param   array $permissions
     * @return  bool
     */
    public function isGranted(Space $space, ObjectIdentity $object, User $user, $permissions = null )
    {

        // NOTE removed all checks and exceptions for brevity sake

        // Get all permission types to create a range
        $permissionRange = array_map('strtoupper',EloquentPermissionType::all()->lists('slug'));

        // Set full range and permission bitmask
        $permissionsBitmask  = new Bitmask( $permissionRange, $permissions );

        // Get group ids for a workspace
        $groupsIds  = EloquentSecurityGroup::where('space_id', '=', $space->id)->select(['id', 'permission_level_id'])->get()->toArray();

        /**
         * Return all the security groups to which the user belongs to create the Security Identities or roles
         * @return EloquentSecurityGroupMember|null
         */
        $sids = EloquentSecurityGroupMember::whereHas('group', function ($q) use ($groupsIds, $user)
        {
            $q->whereIn('security_group_id', array_fetch($groupsIds, 'id'))
                ->where('user_id', $user->uuid);
        })->get();

        /**
         * Return all the permissions maps for the permission levels from the security groups
         * Query to find any permissions maps for permission level and object id
         * @return EloquentPermissionMap|null
         */
        $permissionMaps =  EloquentPermissionMap::whereHas('level', function ($q) use ($groupsIds, $object)
        {
            $q->whereIn('level_id', array_fetch($groupsIds, 'permission_level_id'))
                ->where('object_type_id', $object->getIdentifier());
        })->get()->toArray();

        // Return true if any of bitmask match permission bitmask
        foreach($permissionMaps as $map)
        {
            if ( ( $map['permission_mask'] & $permissionsBitmask->value ) == $permissionsBitmask->value ) return true;
        }

        return false;

    }

@JarekTkaczyk I know I said, but thank you for all your help. I'm sure you have better things to do so your time is very much appreciated.

@nolros it's sunday morning here and I can't get that last question of yours.

Please write it in one phrase, what you want to achieve ;)

@JarekTkaczyk sorry, is it possible to place these two into one query with a join or should I keep them separate? I think teh answer is separate

Summary, groups have a permission_level_id (Role) key which is used to call permission map and they have their own group id which is used for security members i.e. groups have ids for both.

        $sids = EloquentSecurityGroupMember::whereHas('group', function ($q) use ($groupsIds, $user)
        {
            $q->whereIn('security_group_id', array_fetch($groupsIds, 'id'))
                ->where('user_id', $user->uuid);
        })->get();

        /**
         * Return all the permissions maps for the permission levels from the security groups
         * Query to find any permissions maps for permission level and object id
         * @return EloquentPermissionMap|null
         */
        $permissionMaps =  EloquentPermissionMap::whereHas('level', function ($q) use ($groupsIds, $object)
        {
            $q->whereIn('level_id', array_fetch($groupsIds, 'permission_level_id'))
                ->where('object_type_id', $object->getIdentifier());
        })->get()->toArray();

@nolros I would guess it is possible but not necessarily more readable. Show me the tables that are involved and I'll show you the code

@JarekTkaczyk

Jarek, here are the tables. Please don’t feel obligated. It works as outlined, but I’m always interested in learning best practices.

PERMISSIONS http://www.cmosolution.com/laracasts/1stPermissionRelationship.png

USER http://www.cmosolution.com/laracasts/2ndGroupMember.png