11

I have a little problem with this recursive CTE, it works fine except when I have a user without root readable rights means no entry for this element. So if I run this query on a user with rights just on the leaves inside the tree the level part of this query won't work correctly.

It will show the real level hierarchy for example 6 but its the top first readable element for him 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 a user now just have read rights to id 8 and 9 the level from CTE stays at 2 for id 8 and 3 for id 9 but I need for id 8 level 1 if there is no one before

Improve this question
5
  • Can you show some data and expected results? Commented Oct 28, 2013 at 17:48
  • 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 a user now just have read rights to id 8 and 9 the level from cte stays at 2 for id 8 and 3 for id 9 but i need for id 8 level 1 if there is no one before Commented Oct 28, 2013 at 17:57
  • thx for the hind updated accordingly now Commented Oct 28, 2013 at 18:12
  • Can you, please, add the data for the SourceTable? Otherwise it's hard to understand what do You have as a source for the recursive CTE. Commented Oct 31, 2013 at 16:08
  • Without values of the parentID it's hard to create any valid script... If you will add those values, the solution can be found Commented Nov 5, 2013 at 6:08

6 Answers 6

Reset to default
4

You haven't told us how you know whether a user has rights to a given id. That is a necessary piece of information. I'm going to put some code below that assumes you add a column to your query called hasRights and that this column will have a zero value if the user does not have rights and a value of one if they do. You may need to tweak this, since I have no data to test with but hopefully it will get you close.

Basically, the query is altered to only add 1 to the level if the user has rights. It also only adds to the sort path if the user has rights, otherwise an empty string is appended. So, if ids 8 and 9 are the only items the user has access to, you should see levels of 1 and 2 and sort paths similar to '5/8/9' rather than '5/6/8/9'. If you still aren't able to get it working, it would help us tremendously if you posted a 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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You requite something like if the higher level(0 or 1 ) is not in existence, then the next level become the higher level..

If yes, then you have to do this when the final result

insert all the results in temp table lets say #info (with same characteristics of data)

Now after all final data ready in the table,

Please check from the top.

Select * from #info where level= 0

if this returns 0 rows then you have to update each records level. to (level = level -1)

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

I hope this will help :)

Please reply if you are looking for something else.

Improve this answer

Comments

0

Try to perform the following select and let me know if it 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
Improve this answer

Comments

0

Is conversion of your table into hierarchical types an option:

or

  • xml data type
Improve this answer

Comments

0

SOME TIMES option (maxrecursion 10000); is very useful
I don't have time to read your problem but here snipped

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
Improve this answer

Comments

0

I'm sorry to be a party pooper and spoil the fun of creating such an interesting piece of SQL, but perhaps you should load all relevant access data into your application and determine the user levels in the application?

I bet it would result in more maintainable code..

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.