SQL Server – Fetch Tree Structure with Conditions

performancequeryquery-performancesql server

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)

Best Answer

WITH cte AS ( SELECT * 
              FROM tree 
              WHERE id = @starting_node
                  UNION ALL
              SELECT tree.* 
              FROM tree, cte
              WHERE tree.parent_id = cte.id
                AND cte.IsCollapsed = 'expanded' -- if collapsed then stop
            )
Related Question