I need to make a tree structure
create table Dirs ( Id uniqueidentifier primary key not null default (newid()), Name nvarchar(30) not null, CreatedDate datetime not null default getdate(), ModifiedDate datetime not null default getdate(), IsCollapsed bit default dbo.randbit(newid()), ParentId uniqueidentifier default null )
I want to fetch all child nodes from specific node (by id for example), but filter out closed directories' children keeping them itself in output?
e.g. given structure
root - Dir1 (v) -Dir2 (v) -Dir3(x) -Dir4(v) -Dir5(v)
x - means that directory is collapsed,
v - means that directory is expanded.
Expected output is:
Dir1 Dir2 Dir3
I have written such query:
create procedure GetNodes @parentId uniqueidentifier = null as begin with dirsrec as ( select * from Dirs where Id = @parentId union all ( select d.Id, d.Name, d.CreatedDate, d.ModifiedDate, d.IsCollapsed, j.id as ParentId from Dirs d join dirsrec as j on d.ParentId = j.Id where d.IsCollapsed = 'false' ) ), dirsec2 as ( select * from Dirs where Id = @parentId union all ( select d.Id, d.Name, d.CreatedDate, d.ModifiedDate, d.IsCollapsed, j.id as ParentId from Dirs d join dirsrec as j on d.ParentId = j.Id where d.IsCollapsed = 'true' ) ) select * from dirsrec union select * from dirsec2 end
It uses 2 recursions. The firstis fetching all opened nodes from given one, the second fetching all closed nodes and then output gets distincted
It works, but I dislike 2 separated queries with almost identical code. I guess it might be done much better
I tried to make a side-effect (Inserting collapsed nodes from within
union all( subquery into temp table and then just union it with final result, but it looks like sql doesn't allow us to make some logic within subqueries)