Iterate through records to build hierarchy

Question

Please consider the following tables. They describe a schools' hierarchy and the notes per student.

users
-------------------------------------------------------------
root_id    obj_id    obj_type    obj_ref_id    obj_role
-------------------------------------------------------------
1          2         student     7             learn   
1          3         student     7             learn
1          1         principal   1             lead
1          4         mentor      1             train teachers
1          5         trainee     4             learn teaching
1          6         trainee     4             learn teaching
1          7         teacher     1             teach
2          8         student     9             learn
2          9         principal   9             lead

notes
--------------------------------------------------------------
note_id    obj_id    note
--------------------------------------------------------------
1          2         foo
2          2         bar
3          2         baz
4          3         lorem
5          8         ipsum

I need to write out the hierarchy and number of notes per user as follows:

-------------------------------------------------------------------------------------------
obj_id   notes  obj_path
-------------------------------------------------------------------------------------------
1        0      principal 1 (lead)
2        3      student 2 (learn) > teacher 7 (teach) > principal 1 (lead)
3        1      student 3 (learn) > teacher 7 (teach) > principal 1 (lead)
4        0      mentor 4 (train teachers) > principal 1 (lead)
5        0      trainee 5 (learn teaching) > mentor 4 (train teachers) > principal 1 (lead)
6        0      trainee 6 (learn teaching) > mentor 4 (train teachers) > principal 1 (lead)
7        0      teacher 7 (teach) > principal 1 (lead)
8        1      student 8 (learn) > principal 2 (lead)
9        0      principal 9 (lead)

For this, I understand that I need to use a loop as follows:

declare cur cursor for 
select obj_id from users order by root_id 
open cur
declare @obj_id int
    fetch next from cur into @id
    while (@@FETCH_STATUS = 0)
    begin
        select obj_role from users where obj_id = @obj_id
        fetch next from cur into @obj_id
    end
close cur
deallocate cur

This is what I have until now, but I do not understand how to go from here. Can someone help me on my way?

score 0 · Answer 1 · answered Oct 22 '18 at 14:24

Understand that using a cursor is going to process each individual record one by one.

Recursive CTE would be a better solution:

Something like:

DECLARE @User TABLE
    (
        [root_id] INT
      , [obj_id] INT
      , [Obj_type] NVARCHAR(100)
      , [obj_ref_id] INT
      , [obj_role] NVARCHAR(100)
    );

DECLARE @Notes TABLE
    (
        [note_id] INT
      , [obj_id] INT
      , [note] NVARCHAR(255)
    );

INSERT INTO @Notes (
                       [note_id]
                     , [obj_id]
                     , [note]
                   )
VALUES ( 1, 2, 'foo  ' )
     , ( 2, 2, 'bar  ' )
     , ( 3, 2, 'baz  ' )
     , ( 4, 3, 'lorem' )
     , ( 5, 8, 'ipsum' );


INSERT INTO @User (
                      [root_id]
                    , [obj_id]
                    , [Obj_type]
                    , [obj_ref_id]
                    , [obj_role]
                  )
VALUES ( 1, 2, 'student', 7, 'learn' )
     , ( 1, 3, 'student', 7, 'learn' )
     , ( 1, 1, 'principal', 1, 'lead' )
     , ( 1, 4, 'mentor', 1, 'train teachers' )
     , ( 1, 5, 'trainee', 4, 'learn teaching' )
     , ( 1, 6, 'trainee', 4, 'learn teaching' )
     , ( 1, 7, 'teacher', 1, 'teach' )
     , ( 2, 8, 'student', 9, 'learn' )
     , ( 2, 9, 'principal', 9, 'lead' );

WITH [Hierarchy]
AS ( SELECT [obj_id] AS [root_obj]
          , [obj_ref_id] AS [root_obj_ref]
          , [obj_id]
          , [obj_ref_id]
          , CONVERT(
                       NVARCHAR(MAX)
                     , [Obj_type] + ' ' + CONVERT(NVARCHAR, [obj_id]) + ' ('
                       + [obj_role] + ')'
                   ) AS [obj_path]
     FROM   @User
     UNION ALL
     SELECT     [a].[root_obj]
              , [a].[root_obj_ref]
              , [b].[obj_id]
              , [b].[obj_ref_id]
              , [a].[obj_path] + ' > ' + [b].[Obj_type]
                + CONVERT(NVARCHAR, [b].[obj_id]) + ' (' + [b].[obj_role] + ')' AS [obj_path]
     FROM       [Hierarchy] [a]
     INNER JOIN @User [b]
         ON [b].[obj_id] = [a].[obj_ref_id]
     WHERE      [a].[obj_id] <> [a].[obj_ref_id] ) --Here, basically continue the recursion while the record isn't referencing itself. The final will include that self referencing record.
SELECT   [Hierarchy].[root_obj] AS [obj_id]
       , (
             SELECT COUNT(*)
             FROM   @Notes
             WHERE  [obj_id] = [Hierarchy].[root_obj]
         ) AS [notes] --Here we'll go out and get the count of notes.
       , [Hierarchy].[obj_path]
FROM     [Hierarchy]
WHERE    [Hierarchy].[obj_id] = [Hierarchy].[obj_ref_id]  --Then we only went those records built up to the final record that was referencing itself.
ORDER BY [Hierarchy].[root_obj];

Iterate through records to build hierarchy

1 Answers1