MySQL Hierarchy – Getting Root Node for Given Leaf IDs

My MySQL knowledge is rusty (at best), but subqueries like this can normally be replaced by a JOIN and GROUP BY to achieve the desired result.

Something like this will get you on your way:

SELECT  name,
        qty_ordered,
        item_id,
        original_price,
        discount_percent,
        price,
        tax_percent,
        MAX(CASE WHEN mea.attribute_code = 'cip' THEN mcpev.value END) AS cip,
        MAX(CASE WHEN mea.attribute_code = 'cip7' THEN mcpev.value END) AS cip7,
        MAX(CASE WHEN mea.attribute_code = 'gtin_13' THEN mcpev.value END) AS gtin13
FROM    magento_sales_flat_order_item msfoi
INNER JOIN magento_catalog_product_entity_varchar mcpev
  ON msfoi.product_id = mcpev.entity_id
INNER JOIN magento_eav_attribute mea
  ON mcpev.attribute_id = mea.attribute_id
WHERE   order_id = 1
GROUP BY name, qty_ordered, item_id, original_price, discount_percent, price, tax_percent  

Using this model, I have come up with a way to query the Pages table in the following manner:

SELECT
  p.*
FROM
  dbo.Pages AS p
  CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, @PermissionName) AS ps
WHERE
  ps.IsAllowed = 1
;

The GetPermissionStatus inline table-valued function's result can be either an empty set or one single-column row. When the result set is empty, that means that there are no non-NULL entries for the specified page/user/permission combination. The corresponding Pages row is automatically filtered out.

If the function does return a row, then its only column (IsAllowed) will contain either 1 (meaning true) or 0 (meaning false). The WHERE filter additionally checks that the value must be 1 for the row to be included in the output.

What the function does:

• walks the Pages table up the hierarchy to collect the specified page and all its parents into one row set;

• builds another row set containing all the roles the specified user is included in, along with one of the permission columns (but only non-NULL values) – specifically the one corresponding to the permission specified as the third argument;

• finally, joins the first and second set via the RolePages table to find the complete set of explicit permissions matching either the specified page or any of its parents.

The resulting row set is sorted in the ascending order of permission values and the topmost value is returned as the result of the function. Since nulls are filtered out at an earlier stage, the list can contain just 0s and 1s. Thus, if there is at least one "deny" (0) in the list of permissions, that will be the result of the function. Otherwise the topmost result will be 1, unless the roles corresponding to the selected pages happen to have no explicit "allows" either or there are just no matching entries for the specified page and user at all, in which case the result will be an empty row set.

This is the function:

CREATE FUNCTION dbo.GetPermissionStatus
(
  @PageId int,
  @UserId int,
  @PermissionName varchar(50)
)
RETURNS TABLE
AS
RETURN
(
  WITH
    Hierarchy AS
    (
      SELECT
        p.Id,
        p.ParentId
      FROM
        dbo.Pages AS p
      WHERE
        p.Id = @PageId

      UNION ALL

      SELECT
        p.Id,
        p.ParentId
      FROM
        dbo.Pages AS p
        INNER JOIN hierarchy AS h ON p.Id = h.ParentId
    ),
    Permissions AS
    (
      SELECT
        ur.Role_Id,
        x.IsAllowed
      FROM
        dbo.UserRoles AS ur
        INNER JOIN Roles AS r ON ur.Role_Id = r.Id
        CROSS APPLY
        (
          SELECT
            CASE @PermissionName
              WHEN 'Create' THEN [Create]
              WHEN 'Read'   THEN [Read]
              WHEN 'Update' THEN [Update]
              WHEN 'Delete' THEN [Delete]
            END
        ) AS x (IsAllowed)
      WHERE
        ur.User_Id = @UserId AND
        x.IsAllowed IS NOT NULL
    )
  SELECT TOP (1)
    perm.IsAllowed
  FROM
    Hierarchy AS h
    INNER JOIN dbo.RolePages AS rp ON h.Id = rp.Page_Id
    INNER JOIN Permissions AS perm ON rp.Role_Id = perm.Role_Id
  ORDER BY
    perm.IsAllowed ASC
);

Test case

• DDL:

  CREATE TABLE dbo.Users (
    Id       int          PRIMARY KEY,
    Name     varchar(50)  NOT NULL,
    Email    varchar(100)
  );
  
  CREATE TABLE dbo.Roles (
    Id       int          PRIMARY KEY,
    Name     varchar(50)  NOT NULL,
    [Create] bit,
    [Read]   bit,
    [Update] bit,
    [Delete] bit
  );
  
  CREATE TABLE dbo.Pages (
    Id       int          PRIMARY KEY,
    ParentId int          FOREIGN KEY REFERENCES dbo.Pages (Id),
    Name     varchar(50)  NOT NULL
  );
  
  CREATE TABLE dbo.UserRoles (
    User_Id  int          NOT NULL  FOREIGN KEY REFERENCES dbo.Users (Id),
    Role_Id  int          NOT NULL  FOREIGN KEY REFERENCES dbo.Roles (Id),
    PRIMARY KEY (User_Id, Role_Id)
  );
  
  CREATE TABLE dbo.RolePages (
    Role_Id  int          NOT NULL  FOREIGN KEY REFERENCES dbo.Roles (Id),
    Page_Id  int          NOT NULL  FOREIGN KEY REFERENCES dbo.Pages (Id),
    PRIMARY KEY (Role_Id, Page_Id)
  );
  GO
  

• Data inserts:

  INSERT INTO
    dbo.Users (ID, Name)
  VALUES
    (1, 'User A')
  ;
  INSERT INTO
    dbo.Roles (ID, Name, [Create], [Read], [Update], [Delete])
  VALUES
    (1, 'Role R', NULL, 1, 1, NULL),
    (2, 'Role S', 1   , 1, 0, NULL)
  ;
  INSERT INTO
    dbo.Pages (Id, ParentId, Name)
  VALUES
    (1, NULL, 'Page 1'),
    (2, 1, 'Page 1.1'),
    (3, 1, 'Page 1.2')
  ;
  INSERT INTO
    dbo.UserRoles (User_Id, Role_Id)
  VALUES
    (1, 1),
    (1, 2)
  ;
  INSERT INTO
    dbo.RolePages (Role_Id, Page_Id)
  VALUES
    (1, 1),
    (2, 3)
  ;
  GO
  

  So, just one user is used but it is assigned to two roles, with various combinations of permission values between the two roles to test the blending logic on child objects.

  The page hierarchy is very simple: one parent, two children. The parent is associated with one role, one of the children with the other role.

• Test script:

  DECLARE @CurrentUserId int = 1;
  SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, 'Create') AS perm WHERE perm.IsAllowed = 1;
  SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, 'Read'  ) AS perm WHERE perm.IsAllowed = 1;
  SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, 'Update') AS perm WHERE perm.IsAllowed = 1;
  SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, 'Delete') AS perm WHERE perm.IsAllowed = 1;
  

• Cleanup:

  DROP FUNCTION dbo.GetPermissionStatus;
  GO
  DROP TABLE dbo.UserRoles, dbo.RolePages, dbo.Users, dbo.Roles, dbo.Pages;
  GO
  

Results

• for Create:

  Id  ParentId  Name
  --  --------  --------
  2   1         Page 1.1
  

  There was an explicit true for Page 1.1 only. The page was returned according to the "true + not defined" logic. The others were "not defined" and "not defined + not defined" – hence excluded.

• for Read:

  Id  ParentId  Name
  --  --------  --------
  1   NULL      Page 1
  2   1         Page 1.1
  3   1         Page 1.2
  

  An explicit true was found in the settings for Page 1 and for Page 1.1. Thus, for the former it was just a single "true" while for the latter "true + true". There were no explicit read permissions for Page 1.2, so it was another "true + not defined" case. So, all three pages were returned.

• for Update:

  Id  ParentId  Name
  --  --------  --------
  1   NULL      Page 1
  3   1         Page 1.2
  

  From the settings, an explicit true was returned for Page 1 and a false for Page 1.1. For the pages that made it into the output the logic was the same as in case of Read. For the excluded row both false and true were found and so the "false + anything" logic worked.

• for Delete there were no rows returned. The parent and one of the children had explicit nulls in the settings and the other child did not have anything.

Get all permissions

Now if you want just to return all effective permissions, you can adapt the GetPermissionStatus function:

CREATE FUNCTION dbo.GetPermissions(@PageId int, @UserId int)
RETURNS TABLE
AS
RETURN
(
  WITH
    Hierarchy AS
    (
      SELECT
        p.Id,
        p.ParentId
      FROM
        dbo.Pages AS p
      WHERE
        p.Id = @PageId

      UNION ALL

      SELECT
        p.Id,
        p.ParentId
      FROM
        dbo.Pages AS p
        INNER JOIN hierarchy AS h ON p.Id = h.ParentId
    ),
    Permissions AS
    (
      SELECT
        ur.Role_Id,
        r.[Create],
        r.[Read],
        r.[Update],
        r.[Delete]
      FROM
        dbo.UserRoles AS ur
        INNER JOIN Roles AS r ON ur.Role_Id = r.Id
      WHERE
        ur.User_Id = @UserId
    )
  SELECT
    [Create] = ISNULL(CAST(MIN(CAST([Create] AS int)) AS bit), 0),
    [Read]   = ISNULL(CAST(MIN(CAST([Read]   AS int)) AS bit), 0),
    [Update] = ISNULL(CAST(MIN(CAST([Update] AS int)) AS bit), 0),
    [Delete] = ISNULL(CAST(MIN(CAST([Delete] AS int)) AS bit), 0)
  FROM
    Hierarchy AS h
    INNER JOIN dbo.RolePages AS rp ON h.Id = rp.Page_Id
    INNER JOIN Permissions AS perm ON rp.Role_Id = perm.Role_Id
);

The function returns four columns – the effective permissions for the specified page and user. Usage example:

DECLARE @CurrentUserId int = 1;
SELECT
  *
FROM
  dbo.Pages AS p
  CROSS APPLY dbo.GetPermissions(p.Id, @CurrentUserId) AS perm
;

Output:

Id  ParentId  Name      Create Read  Update Delete
--  --------  --------  ------ ----- ------ ------
1   NULL      Page 1    0      1     1      0
2   1         Page 1.1  1      1     0      0
3   1         Page 1.2  0      1     1      0