Recursive CTE check if record exists at any level then get all children (folder structure)-CodePudding

I'm trying to check using a recursive CTE if a user has access to a folder and if they do, then the folder and all children

The structure is basic

CREATE TABLE [dbo].[Folders](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](1024) NOT NULL,
  [ParentId] [int] NULL)

This is the table that links a user to a folder

CREATE TABLE [dbo].[FolderAccess](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [UserId] [bigint] NOT NULL,
  [FolderId] [int] NOT NULL)

Using the following example data

SELECT * FROM Folders

Id	ParentId	Name
1	NULL	Root
2	1	Sub folder 1
3	2	Sub folder 2
4	3	Sub folder 3
5	4	Sub folder 4
6	5	Sub folder 5

SELECT UserId, FolderId FROM FolderAccess

UserId	FolderId
1	3

The user above has access to folder 3 so indirectly has access to folder 4,5,6

I should be able to put any FolderId 3,4,5,6 and get results but using folder id 2 would return nothing as that is higher up the hierarchy.

DECLARE @FolderId INT = 3
;WITH TopDownFolderHierarchy (Id, ParentId , Name)
AS
(
    SELECT fold.Id, fold.ParentId, fold.Name FROM Folders fold 
    WHERE fold.ID = @FolderId
    UNION ALL
    SELECT parents.Id, parents.ParentId, parents.Name FROM Folders parents 
    INNER JOIN TopDownFolderHierarchy cte on cte.Id = parents.ParentId
    
)

Thanks

CodePudding user response：

The query below tries first to list folders the user has access to then the required hierarchy among accessible folders

DECLARE @UserId INT = 1;
DECLARE @FolderId INT = 5;

WITH
AccessibleHierarchy (Id, ParentId , Name) AS
(
    SELECT fold.Id, fold.ParentId, fold.Name FROM Folders fold 
    WHERE fold.ID in (select [FolderId] from [FolderAccess] where [UserId] = @UserId)
    UNION ALL
    SELECT parents.Id, parents.ParentId, parents.Name FROM Folders parents 
    INNER JOIN AccessibleHierarchy cte on cte.Id = parents.ParentId
),
QueriedHierarchy (Id, ParentId , Name) AS
(
    SELECT fold.Id, fold.ParentId, fold.Name FROM AccessibleHierarchy fold 
    WHERE fold.ID = @FolderId
    UNION ALL
    SELECT parents.Id, parents.ParentId, parents.Name FROM Folders parents 
    INNER JOIN QueriedHierarchy cte on cte.Id = parents.ParentId
)
select * from QueriedHierarchy

db<>fiddle