Get All The Child Details of Parent Menu

Question

I am working on a menu access permission system where an admin can assign users to a group that consists menu access permission. It's simple when it comes to give permission to users individually. But for group-wise permission, I've few criteria to be matched. So here are the tables structure and sample data:

----USER DETAILS----
CREATE TABLE HRD.USER_DETAILS
(
  EMPNO VARCHAR2(20 BYTE),
  ENAME VARCHAR2(40 BYTE),
  ENTRY_DATE DATE,
  STATUS INT
);

EMP-0001    John    6/10/2018   1
EMP-0002    Jack    5/12/2018   1

----GROUP DETAILS----
CREATE TABLE HRD.GROUP_DETAILS
(
  GROUPNO VARCHAR2(20 BYTE),
  GROUPNAME VARCHAR2(40 BYTE),
  DETAILS VARCHAR2(100 BYTE),
  ENTRY_DATE DATE,
  STATUS INT
);

GROUP-0001  GROUP-1     1/1/2018    1
GROUP-0002  GROUP-2     1/1/2018    1

----MENU DETAILS----
CREATE TABLE HRD.MENU_DETAILS
(
  MENUNO VARCHAR2(20 BYTE),
  MENUNAME VARCHAR2(40 BYTE),
  DETAILS VARCHAR2(100 BYTE),
  PARENT VARCHAR2(10 BYTE),
  ENTRY_DATE DATE,
  STATUS INT
);

1001    User Details        0   1/1/2018    1
1002    Add User        1001    1/1/2018    1
1003    Department Details      0   1/1/2018    1
1004    Add Department      1003    1/1/2018    1

----ASSIGN MENU DETAILS----
CREATE TABLE HRD.ASSIGN_MENU_DETAILS
(
  GROUPNO VARCHAR2(20 BYTE),
  MENUNO VARCHAR2(20 BYTE),
  DETAILS VARCHAR2(100 BYTE),
  ENTRY_DATE DATE,
  STATUS INT
);

GROUP-0001  1001        1/1/2018    1
GROUP-0001  1004        1/1/2018    1

----USER GROUP DETAILS----
CREATE TABLE HRD.USER_GROUP_DETAILS
(
  EMPNO VARCHAR2(20 BYTE),
  GROUPNO VARCHAR2(20 BYTE),
  DETAILS VARCHAR2(100 BYTE),
  ENTRY_DATE DATE,
  STATUS INT
);

EMP-0001    GROUP-0001      1/1/2018    1
EMP-0001    GROUP-0002      1/1/2018    1

So using the below query, I get the following:

SELECT M.EMPNO, M.GROUPNO, K.MENUNO, Q.MENUNAME FROM USER_GROUP_DETAILS m INNER JOIN ASSIGN_MENU_DETAILS k ON K.GROUPNO = M.GROUPNO
LEFT JOIN (SELECT P.MENUNO, P.MENUNAME FROM MENU_DETAILS p WHERE P.PARENT = '0' OR P.PARENT <> '0') q ON Q.MENUNO = K.MENUNO 
GROUP BY M.EMPNO, M.GROUPNO, K.MENUNO, Q.MENUNAME;

EMP-0001    GROUP-0001  1001    User Details
EMP-0001    GROUP-0001  1004    Add Department

As the user has no menu permission for GROUP-0002, then there should be no menu shown with the query. Here is the most important thing: The GROUP-0001 has two menu permission 1) User Details 2) Add Department

The user details menu is a parent and the add department is a child menu of the Department Details menu. So what I am expecting, whenever there is a child menu assigned, then the parent menu should be visible as well in the query. Now the Add Department menu has the parent Department Details, so the parent must be reflected as well.

I am not sure if I'll do it with a query or C# but tried with C# code as well but seems like the logic isn't built perfectly.

C#:

foreach (var parent in GetViewModel())
{
   foreach (var child in GetViewModel2(parent.Parent))
   {
       sampleDynamicNav = new List<NavBarItem> {
            new NavBarItem {
                D = 1, Text = parent.MenuName, Icon = new ItemIcon {Default =  HRMS_New_VS2.Properties.Resources.nav_new_home, Hover = HRMS_New_VS2.Properties.Resources.nav_new_home, Selected = HRMS_New_VS2.Properties.Resources.nav_new_home}, ToolTip = "tooltip Main Menu", Height = 40,

                Childs = new List<NavBarItem> {
                    new NavBarItem {ID = child.MenuNo, Text = child.MenuName, Height = 30 },
               }
           }
       };
   }
}

public IEnumerable<EmpViewModel> GetParent()
{
     List<EmpViewModel> lstEmp = new List<EmpViewModel>();

     string query = "SELECT M.EMPNO, M.GROUPNO, K.MENUNO, Q.MENUNAME, Q.PARENT FROM USER_GROUP_DETAILS m INNER JOIN ASSIGN_MENU_DETAILS k ON K.GROUPNO = M.GROUPNO " +
                    "LEFT JOIN (SELECT P.MENUNO, P.MENUNAME, P.PARENT FROM MENU_DETAILS p) q ON Q.MENUNO = K.MENUNO WHERE M.EMPNO = 'EMP-0001' " +
                    "GROUP BY M.EMPNO, M.GROUPNO, K.MENUNO, Q.MENUNAME, Q.PARENT";

      DataTable dt = SelectData(query);

      if (dt != null && dt.Rows.Count > 0)
      {
          foreach (DataRow dr in dt.Rows)
          {
              EmpViewModel bo = new EmpViewModel();
              bo.Parent = dr["PARENT"].ToString();
              bo.MenuName = dr["MENUNAME"].ToString();
              bo.MenuNo = Convert.ToInt32(dr["MENUNO"].ToString());

              lstEmp.Add(bo);
          }
       }

     return lstEmp;
}



public IEnumerable<EmpViewModel> GetChild(string menuNo)
{
    List<EmpViewModel> lstEmp = new List<EmpViewModel>();

    string query = "SELECT M.EMPNO, M.GROUPNO, K.MENUNO, Q.MENUNAME, Q.PARENT FROM USER_GROUP_DETAILS m INNER JOIN ASSIGN_MENU_DETAILS k ON K.GROUPNO = M.GROUPNO " +
                   "LEFT JOIN (SELECT P.MENUNO, P.MENUNAME, P.PARENT FROM MENU_DETAILS p) q ON Q.MENUNO = K.MENUNO WHERE M.EMPNO = 'EMP-0001' AND Q.PARENT = '" + menuNo + "'" +
                   "GROUP BY M.EMPNO, M.GROUPNO, K.MENUNO, Q.MENUNAME, Q.PARENT";

     DataTable dt = SelectData(query);

     if (dt != null && dt.Rows.Count > 0)
     {
         foreach (DataRow dr in dt.Rows)
         {
            EmpViewModel bo = new EmpViewModel();
            bo.Parent = dr["PARENT"].ToString();
            bo.MenuName = dr["MENUNAME"].ToString();
            bo.MenuNo = Convert.ToInt32(dr["MENUNO"].ToString());

            lstEmp.Add(bo);
         }
     }
    return lstEmp;
}

Expected Output - For menu permission: GROUP-0001 for a user

Menu - 01: User Details
Menu - 02: Department Details
           -> Add Department

N.B: A user can be assigned to multiple group and the group may repeat the menu permissions twice. In that case, it should reflect distinct menus keeping the parent and child menus adjacently. For the time being, keeping it aside.

score 2 · Accepted Answer · answered Dec 23 '18 at 18:37

You could use a recursive CTE to get all the parent menus.

WITH 
CTE (MENUNO,
     MENUNAME,
     PARENT) AS
(
SELECT M.MENUNO,
       M.MENUNAME,
       M.PARENT
       FROM USER_DETAILS U
            INNER JOIN USER_GROUP_DETAILS UG
                       ON UG.EMPNO = U.EMPNO
            INNER JOIN ASSIGN_MENU_DETAILS AM
                       ON AM.GROUPNO = UG.GROUPNO
            INNER JOIN MENU_DETAILS M
                       ON M.MENUNO = AM.MENUNO
       WHERE U.EMPNO = 'EMP-0001'
UNION ALL
SELECT M.MENUNO,
       M.MENUNAME,
       M.PARENT
       FROM MENU_DETAILS M
            INNER JOIN CTE C
                       ON C.PARENT = M.MENUNO
)
SELECT DISTINCT
       MENUNO,
       MENUNAME
       FROM CTE;

db<>fiddle

Worked perfectly @sticky bit. See if you can do anything for this one - https://stackoverflow.com/questions/53911780/create-object-in-every-loop-iteration. — AT-2017, Dec 24 '18 at 10:19

Get All The Child Details of Parent Menu

1 Answers1