I need to make a tree structure
Table:
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)
where 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)