Consider the following structure :
alt text http://aeon-dev.org/pap/pap_db.png
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!
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 = ?
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
role_id, not Role in your select clause. FTFYThis 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
)
You could find all distinct roles for user 1 like:
select distinct role_id
from (
select uhr.user_id
, uhr.role_id
from user_has_role uhr
union all
select uhg.user_id
, ghr.role_id
from user_has_group uhg
join group_has_role ghr
on ghr.group_id = uhg.group_id
where not exists
(
select *
from user_has_role uhr
where uhr.user_id = uhg.user_id
and uhr.role_id = ghr.role_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.
