Best way to get first role from user?
Application Type
Reactive

Hello !
I need to get a list of distinct users, and get the first role from each user..
So I will have a list of users and one associated role..

What's the best way to achieve this? Can it be done with an aggregate? How?

Any help is appreciated :)

mvp_badge
MVP
Solution

Sorry, I was making confusion on the query.

This should be the one.




The subquery users gets the users with one of their roles (gets the max role Id for each user).

Then I just INNER JOIN with the Role table to get the name of the role for that id.

Please let me know if this finally solves the problem.


Kind Regards,
João

Hi Ricardo,

You can use SQL instead of aggregate for this.

You can adapt the Tables in the SQL command to your own tables.

SELECT U.RoleId, U.UserId
FROM UserRole U
INNER JOIN
    (SELECT userid, MIN(RoleId) As first_role
    FROM User
    GROUP BY userid) X
ON U.UserId = X.Id AND U.RoleId = X.first_role

Best regards

Yusuf Kerim


The U is the UserRole table? :)
So what this does, is getting all users, grouped by their Id wich makes them to be a single user , and then gives the first role for that group...

And you go into the userRole table getting the record that you got with the min() function, right?

Also, where did userid come from? :/

mvp_badge
MVP

Hi Ricardo,


What do you mean by "first role"? The first role the user had?

As far as I know, the timestamp information is not recorded in the OutSystems metadata so, unless you build it yourself, there's no way to get the oldest role a user has.


In any case, to do such a query, I would go to an advanced SQL, where I would join the user entity to a subquery with TOP 1 of the roles ordered by date.


Kind Regards,
João Marques

The smallest id in User_role should be the first assigned.

mvp_badge
MVP

Although I understand what you are saying, that's not necessarily true.

Besides the fact that the Id does not necessarily mean that it is the oldest one (the record can be updated in a query to another role, for instance, a user "inherits" the roles associated with a group by belonging on that group and the roles a user has via group are not in User_Role entity.

The ultimate "truth" about the roles a user has is on the User_Effective_Role... which is a view.


Kind Regards,
João

Thanks for giving that extra insight. But now i am curious as to how you would build that datetime as you suggested.


this might be something i could use at some point.

Even tho its not the oldest, it works for me. I just want one of the list, doesn´t necessarily needs to be the first! :) So it works like that :p

Thanks! :)

mvp_badge
MVP

If you just need any role, you just need to join with a subquery with the TOP 1, like this:



Don't forget to adjust the output structure to match the SELECT fields in the query.

You can get the User_Effective_Role entity from System, just like the User and the Role entities.


Kind Regards,
João

I just placed email on the selection of the user... what is wrong? :/

mvp_badge
MVP

On the SELECT, it should be roles, instead of maxrole. Sorry, my bad on my query.

But in any case, the order of the fields on your select, don't match the ones on your structure.

On the SELECT, you have your Email is the third field, on the structure it is the last one. They need to be aligned.


Kind Regards,
João

Yeah my bad, while doing it I needed email and added it last and forgot that...By the way, the query worked fine, but it only gave me one user... It should be returning all distinct users, but with one role... :p Is this supposed to do that? :o

(Big thanks for the help, will mark it as solution, and I really need to start looking up for some SQL Joins, when they get like this my mind blows xD )

ou are doing an inner join on a query that only return a top 1.

You should do the inner join outside your subquery.

mvp_badge
MVP

No problem, I'm glad I can help :)

In any case, I made a mistake with the TOP 1, I was still thinking on the way to get the last role.

Give me a few minutes to fix it.


Kind Regards,
João

If I remove Top the query will give me users repeated... 

For example, Administrator will have tons of records, since it has a lot of roles... i need to have only one admin, with one role :/ 

I am going to be honest, I tried to change the subquery and it gave me an error, im not really sure what you mean by moving the subquery out of the INNER JOIN :(

mvp_badge
MVP
Solution

Sorry, I was making confusion on the query.

This should be the one.




The subquery users gets the users with one of their roles (gets the max role Id for each user).

Then I just INNER JOIN with the Role table to get the name of the role for that id.

Please let me know if this finally solves the problem.


Kind Regards,
João


This is spoon feeding me, but I am really messy with SQL  and I cannot understand this bug right here 

Should I change my output structure? Does not seem like he is complaining about it

mvp_badge
MVP

It should be ON {User}.[Id] = {User_Effective_Role}.[User_Id] . This last part is missing, hence the error.

Yup, Perfect! :D

Seems today is going to be a night about SQL JOINS :p

Thanks for the help, really appreciated! :D

mvp_badge
MVP

Sorry about the confusion, need to get some sleep :P


Good luck,
João

I didn't have much time to try, but i wanted to get it working with the Top 1 and i failed miserably :)


its indeed all about SQL today.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.