MySQL- complex data query in a single statement

Question

Hi,

Consider the following structure :

Ignore the table user_token.

Now, imagine that you need to get all the roles related to an user, wich may be through it's related groups or directly related to him. In case the same role appears related to a group and the user directly, the role related to the user will prevail over the role given by the group.

Is there any chance this could be done in a single query?

Cheers!

Answer 1

You could find all distinct roles for user 1 like:

select  distinct role_id
from    (
        select  1 as prio
        ,       uhr.user_id
        ,       uhr.role_id
        from    user_has_role uhr
        union all
        select  2 as prio
        ,       uhg.user_id
        ,       ghr.role_id
        from    user_has_group uhg
        join    group_has_role ghr
        on      ghr.group_id = uhg.group_id
        ) user2role    
where   user_id = 1

Not sure how a role related to a user should "prevail", but you can assign priorities to them in the union.

Answer 2

try this:

  Select role_id 
  From user_has_role
  Where userId = @UserId
  Union
  Select role_id 
  From user_has_group g
     Join Group_has_Role gr
        On gr.GroupId = g.GroupId 
  Where userId = @UserId

Answer 3

This query will get every role a single user has either directly or given by a group

SELECT * FROM ROLE WHERE ID IN (
    SELECT ROLE_ID
    FROM USER_HAS_ROLE 
    WHERE USER_ID = 1
    UNION
    SELECT ROLE_ID
    FROM USER_HAS_GROUP UG
    INNER JOIN GROUP_HAS_ROLE GR ON UG.GROUP_ID = GR.GROUP_ID
    WHERE USER_ID = 1
)

Answer 4

Use:

   SELECT DISTINCT r.name AS role_name
     FROM USER u
LEFT JOIN USER_HAS_ROLE uhr ON uhr.user_id = u.id 
LEFT JOIN USER_HAS_GROUP uhg ON uhg.user_id = u.id 
LEFT JOIN GROUP_HAS_ROLE ghr ON ghr.group_id = uhg.group_id 
LEFT JOIN ROLE r ON r.id = uhr.role_id
                 OR r.id = ghr.role_id
    WHERE u.username = ?