Conditional WHERE clause. Show all projects to Administrator or assigned projects to STAFF members

user2924127 0
Viewed 44

I have a PROJECT table. Users are assigned to different projects. Users can also have the role of either STAFF or ADMINISTRATOR. If they are STAFF, they should only see projects that are assigned to them. If they are an ADMINISTRATOR, they should be able to see all the projects.

Project Table
-project_id (pk)

User Table
-user_id (pk)

Project User Table
-project_user_id (pk)
-project_id (fk)
-user_id (fk)

Role Table
-role_id (pk)
-rolename (STAFF or ADMINISTRATOR)

User Role Table
-user_role_id (pk)
-user_id (fk)
-role_id (fk)

Now, depending on the user's role (they can be STAFF or ADMINISTRATOR), I want to show them the appropriate projects. This is how I have tried to build the query, but am stuck on how to apply the condition.

Step 1)

--select all projects
select
    pu.project_id
from
   project p

Step 2)

--select all projects and the users assigned to them
    select
        pu.project_id
    from
       project p
    left join
       project_user pu
     on
       p.project_id = pu.project_id

Step 3)

--select all projects and the users assigned to them and the users role
    select
        pu.project_id,
        pu.user_id,
        r.rolename
    from
       project p
    left join
       project_user pu
     on
       p.project_id = pu.project_id
    left join
       user_role ur
    on
       ur.user_id = pu.user_id
    inner join
       role r
    r.role_id = ur.role_id

This is where I am now stuck. How do I apply the condition to check if this user is STAFF or ADMINISTRATOR and select the appropriate projects?

oracle sql
1 Answers

With sample data like below:

WITH
    projects AS
        (
            Select 'A'  "PROJECT_ID" From Dual Union All
            Select 'B'  From Dual Union All
            Select 'C'  From Dual Union All
            Select 'D'  From Dual Union All
            Select 'E'  From Dual Union All
            Select 'F'  From Dual Union All
            Select 'G'  From Dual Union All
            Select 'H'  From Dual Union All
            Select 'I'  From Dual 
        ),
    users AS
        (
            Select '1'  "USER_ID" From Dual Union All
            Select '2'  From Dual Union All
            Select '3'  From Dual Union All
            Select '4'  From Dual
            
        ),
    usr_projects AS
        (
            Select 'A_1' "PROJ_USER_ID", 'A' "PROJECT_ID", 1 "USER_ID" From Dual Union All
            Select 'B_1' "PROJ_USER_ID", 'B' "PROJECT_ID", 1 "USER_ID" From Dual Union All
            Select 'C_2' "PROJ_USER_ID", 'C' "PROJECT_ID", 2 "USER_ID" From Dual Union All
            Select 'D_3' "PROJ_USER_ID", 'D' "PROJECT_ID", 3 "USER_ID" From Dual Union All
            Select 'E_3' "PROJ_USER_ID", 'E' "PROJECT_ID", 3 "USER_ID" From Dual Union All  
            Select 'F_4' "PROJ_USER_ID", 'F' "PROJECT_ID", 4 "USER_ID" From Dual Union All
            Select 'G_2' "PROJ_USER_ID", 'G' "PROJECT_ID", 2 "USER_ID" From Dual Union All
            Select 'H_3' "PROJ_USER_ID", 'H' "PROJECT_ID", 3 "USER_ID" From Dual Union All
            Select 'I_1' "PROJ_USER_ID", 'I' "PROJECT_ID", 1 "USER_ID" From Dual        
        ),
    roles AS
        (
            Select 'R1' "ROLE_ID", 'ADMIN' "ROLE_NAME" From Dual Union All
            Select 'R2', 'STUFF'    From Dual
        ),
    usr_roles AS
        (
            Select '1_R1' "USER_ROLE_ID", 1 "USER_ID", 'R1' "ROLE_ID" From Dual Union All
            Select '2_R2' "USER_ROLE_ID", 2 "USER_ID", 'R2' "ROLE_ID" From Dual Union All
            Select '3_R2' "USER_ROLE_ID", 3 "USER_ID", 'R2' "ROLE_ID"  From Dual Union All
            Select '4_R2' "USER_ROLE_ID", 4 "USER_ID", 'R2' "ROLE_ID"  From Dual
        ),

... you can create cte with all the data you need ...

    users_roles_projects AS
        (
            SELECT
                r.ROLE_NAME "ROLE_NAME",
                r.ROLE_ID "ROLE_ID",
                ur."USER_ROLE_ID",
                u.USER_ID "USER_ID",
                up.PROJ_USER_ID "PROJ_USER_ID",
                p.PROJECT_ID "PROJECT_ID"
            FROM
                usr_projects up
            INNER JOIN
                users u ON(u.USER_ID = up.USER_ID)
            INNER JOIN
                usr_roles ur ON(ur.USER_ID = u.USER_ID)
            INNER JOIN
                roles r ON(r.ROLE_ID = ur.ROLE_ID)
            INNER JOIN
                projects p ON(p.PROJECT_ID = up.PROJECT_ID)
            ORDER BY 
              r.ROLE_ID,
              u.USER_ID,
              p.PROJECT_ID      
        )

... here is the resulting dataset...

/*  R e s u l t :
ROLE_NAME ROLE_ID USER_ROLE_ID USER_ID PROJ_USER_ID PROJECT_ID
--------- ------- ------------ ------- ------------ ----------
ADMIN     R1      1_R1         1       A_1          A          
ADMIN     R1      1_R1         1       B_1          B          
ADMIN     R1      1_R1         1       I_1          I          
STUFF     R2      2_R2         2       C_2          C          
STUFF     R2      2_R2         2       G_2          G          
STUFF     R2      3_R2         3       D_3          D          
STUFF     R2      3_R2         3       E_3          E          
STUFF     R2      3_R2         3       H_3          H          
STUFF     R2      4_R2         4       F_4          F      
*/

Now you can select your data filtered by the active user like here:

SELECT
    urp.*
FROM
    usr_roles ur
INNER JOIN
    users_roles_projects urp ON(urp.USER_ROLE_ID = ur.USER_ROLE_ID)
WHERE
    urp.USER_ID = :ActiveUser
ORDER BY 
              urp.ROLE_ID,
              urp.USER_ID,
              urp.PROJECT_ID

The result for :ActiveUser = 1 is:

--  ROLE_NAME ROLE_ID USER_ROLE_ID USER_ID PROJ_USER_ID PROJECT_ID
--  --------- ------- ------------ ------- ------------ ----------
--  ADMIN     R1      1_R1         1       A_1          A          
--  ADMIN     R1      1_R1         1       B_1          B          
--  ADMIN     R1      1_R1         1       I_1          I

... and for :AcctiveUser = 4

--  ROLE_NAME ROLE_ID USER_ROLE_ID USER_ID PROJ_USER_ID PROJECT_ID
--  --------- ------- ------------ ------- ------------ ----------
--  STUFF     R2      4_R2         4       F_4          F

And if you want the ADMIN user to see all the data then just change the where clause to:

WHERE
    urp.USER_ID = :ActiveUser OR
    CASE WHEN EXISTS (SELECT  USER_ROLE_ID From usr_roles Where USER_ID = :ActiveUser And ROLE_ID IN(SELECT ROLE_ID From roles Where ROLE_NAME = 'ADMIN')) THEN 'ADM' ELSE 'STF' END = 'ADM'

Regards...

d r0
Related