Filter aggregate for user role"highest" role

Hi,


I'm working with an aggregate of  my users where I've joined the user_effective_role into the agg.  I'm trying to get a list of users filtered by their max effective role. 

For example,  I have the user Adam that has roles - 11, 12,13 -  so in my results i have 3 entries for him.  I only want the entry 13 returned.  With another user - Jerry that has role 11 only,  I want to see his entry for 11 back.   If I was filtering for role-ID 11 - I would only want to see Jerry, and not Adam -as he has higher roles and should only show if my filter value was 13. 

Alternatively -  I'd like to get the list of users back who only have the specified role ID back, and not others.  EG - Only return users who have just Role 11. 

Any help appreciated. 


Cheers



Hi Dev,


In your aggregate use join on User, User_Role and Role table.

Create Group by Attribute for User.Username and Max of Role.Id

 

Regards,

Saugat

Hi Dev,

You should use advance SQL instead of aggrigate because what you want is filtering your grouped data which is not possible with where condition supported by aggregate.

You have to use having clause in your sql but unfortunatly it is supported by aggregates.

Your query will looks something like this

SELECT {User}.[Id] Id, {User}.[Name] Name, Max({User_Effective_Role}.[Role_Id]) RoleId
FROM ({User}
    Left JOIN {User_Effective_Role} ON ({User}.[Id] = {User_Effective_Role}.[User_Id])) 
GROUP BY {User}.[Name], {User}.[Id]
HAVING Max({User_Effective_Role}.[Role_Id])=@RoleId

There is one issue with your approach, because role id are auto increment numbers so while creating these roles you have to make sure that you create your lowest priority role first and so on till highest priority role. Only in that case your above logic will work.

Nikhil Gaur wrote:

There is one issue with your approach, because role id are auto increment numbers so while creating these roles you have to make sure that you create your lowest priority role first and so on till highest priority role. Only in that case your above logic will work.

IDs should never be treated as meaningful. You have no control over what the order the roles will be created in. None. What's the plan for deployment? Deploy a version, make a new role, deploy another version, and so on? What happens when you need to move from one environment to another and it creates all the roles at the same time?

If you need a role hierarchy, make a role hierarchy. Do not assume the IDs mean anything.

J.Ja

Dev Armstrong -

There is no such thing as a "max effective role" unless you create that concept.

Make an Entity (let's call it... "RoleLevel") with an RoleId attribute and a Level attribute (as an integer).

Populate this, with a backoffice screen or maybe a bootstrap time, so that it has the records you need with the hierarchy of roles.

Then, to find the "max effective role" for a user, make an aggregate joining either user_role or user_effective_role to RoleLevel on the RoleId, filter on the user id, sort by RoleLevel.Level DESCENDING, then set Max Records = 1.

J.Ja