Hi All,
There are two tables- Users and Roles
I want to fetch count of users in each role and show to the end user.
I have to use sql for the same.
Could some one suggest on how to use group by and count in sql queries to achieve above.
Hello,
Are you using the built-in User and Role entities? If so, you can use this query:
SQL command:
SELECT {Role}.[Id], {Role}.[Name], COUNT({User}.[Id])FROM {User}INNER JOIN {User_Effective_Role} ON {User_Effective_Role}.[User_Id] = {User}.[Id]INNER JOIN {Role} ON {Role}.[Id] = {User_Effective_Role}.[Role_Id]GROUP BY {Role}.[Id], {Role}.[Name]
Notice that I used the "User_Effective_Role" entity instead of "User_Role ". This ensures that roles assigned via user groups are also taken into consideration.
Hi Aurelio,
User table is built in and role table is custom created.
Also I am using structure. So how do I assign the count of users to the 'NumberOfUsers' attribute of the structure?
You should be able to easily adapt the query I shared to use your custom table instead of the built-in ones.
As for your second question, all you have to do is make sure that your SQL query selects the columns in the same order as the structure's attributes, and that the data types match. In the image I shared above, the platform will automatically map the value of "COUNT({User}.[Id]) " to the "RoleCount" attribute.
You also need this table to link both tables.
Hello @Simran Vaswani,
Please find the attached OML, I have created a server action Called "SQL" which has the required SQL query in it, I just created a structure for the output of SQL.
I hope this solves your problem, If you need any further clarification, please let me know.
Also, If you find the OML helpful please mark this as a solution so that it would help anyone else facing a similar problem.
Thank you,
Saad Ahmed
There are a few problems in your implementation:
Thank you @Aurelio Junior and @Paulo Torres, your suggestions worked for me
Glad to help!