All geek questions in one place

Recursive cte sql for hierarchy level

I have a little problem with this recursive CTE, it works fine, except when I have a user without root access, there is no record for this item. Therefore, if I run this request with a user with rights only on leaves inside the tree, part of the level of this request will not work correctly.

It will show a real level hierarchy, for example 6, but its top first readable element is for it, so it should be 1.

WITH Tree AS ( SELECT id, parent, 0 AS Level, id AS Root, CAST(id AS VARCHAR(MAX)) AS Sort, user_id FROM SourceTable WHERE parent IS NULL UNION ALL SELECT st.id, st.parent, Level + 1 AS Level, st.parent AS Root, uh.sort + '/' + CAST(st.id AS VARCHAR(20)) AS Sort, st.user_id FROM SourceTable AS st JOIN Tree uh ON uh.id = st.parent ) SELECT * FROM Tree AS t JOIN UserTable AS ut ON ut.id = t.user_id AND ut.user_id = '141F-4BC6-8934' ORDER BY Sort

the level is as follows

 id level 5 0 2 1 7 2 4 2 1 2 6 1 3 2 8 2 9 3

When the user now has read permissions only for identifiers 8 and 9, the CTE level remains equal to 2 for identifiers 8 and 3 for identifier 9, but I need for level 8 level 1 if it is not up to

+6
source share
6 answers

You did not tell us how you know if the user has rights to this identifier. This is the necessary information. I am going to give the code below, which assumes that you add a column to your query called hasRights and that this column will have a null value if the user does not have rights and a unit value if they do. You may need to tweak this as I don’t have data to test, but hopefully it will get you closer.

Basically, the request is modified to add only 1 to the level if the user has rights. It also adds only the sort path if the user has rights, otherwise an empty string is added. So, if identifiers 8 and 9 are the only elements that the user has access to, you should see levels 1 and 2 and sort the paths similar to "5/8/9" and not "5/6/8/9". If you still can’t get it working, it will help us a lot if you put the sample schema on SqlFiddle.

 WITH Tree AS ( SELECT id, parent, 0 AS Level, id AS Root, hasRights AS HasRights, CAST(id AS VARCHAR(MAX)) AS Sort, user_id FROM SourceTable WHERE parent IS NULL UNION ALL SELECT st.id, st.parent, Level + st.hasRights AS Level, st.parent AS Root, st.hasRights AS HasRights, uh.sort + CASE st.hasRights WHEN 0 THEN '' ELSE '/' + CAST(st.id AS VARCHAR(20)) END AS Sort, st.user_id FROM SourceTable AS st JOIN Tree uh ON uh.id = st.parent ) SELECT * FROM Tree AS t JOIN UserTable AS ut ON ut.id = t.user_id AND ut.user_id = '141F-4BC6-8934' ORDER BY Sort
+2
source

You are repeating something like, if a higher level (0 or 1) does not exist, then the next level becomes higher.

If yes, then you should do it when the end result

insert all the results into the temp table, say #info (with the same data characteristics)

Now, after the final data ready in the table,

Please check on top.

Choose * from #info, where level = 0

if this returns 0 rows, you need to update each level of records. to (level = level -1)

Now again the same thing for Level = 0, then level 1, then level 2, then level 3 in recursion. it will be easy, but not easy to code. So try without recursion, then try the final update.

Hope this helps :)

Please answer if you are looking for something else.

0
source

Try the following choices and let me know if this is your desired result:

 SELECT *, DENSE_RANK() OVER (PARTITION BY t.user_id ORDER BY t.LEVEL ASC) -1 as RelativeUserLevel FROM Tree AS t JOIN UserTable AS ut ON ut.id = t.user_id AND ut.user_id = '141F-4BC6-8934' ORDER BY Sort
0
source

Converting your table to hierarchical types:

or

  • xml data type
0
source

SOME TIME (maxrecursion 10000); very useful
I do not have time to read your problem, but snapped here

 declare cursorSplit Cursor for select String from dbo.SplitN(@OpenText,'~') where String not in (SELECT [tagCloudStopWordText] FROM [tagCloudStopList] where [langID] =@ _langID) option (maxrecursion 10000); open cursorSplit
0
source

I apologize for being a supporter of pooper and spoiling the pleasure of creating such an interesting piece of SQL, but maybe you need to load all the necessary access data into your application and determine the user levels in the application?

I am sure this will lead to more convenient code.

0
source

Source: https://habr.com/ru/post/956887/

More articles:


All Articles