具有层次结构级别的递归CTE SQL [英] Recursive cte sql with for hierarchy level

问题描述

此递归CTE有点问题，它工作正常，除非当我的用户没有root可读权限时，意味着该元素没有条目。因此，如果我对仅在树内叶子上具有权限的用户运行此查询，则该查询的级别部分将无法正常工作。

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.

它将显示真实的 level 层次结构，例如6，但对于他来说，它是最容易阅读的第一元素，因此应该为1。

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

征收el如下

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

当用户现在仅具有对ID 8和9的读取权限时，CTE的级别对于ID 8保持为2，对于ID 9则保持为3，但是如果需要ID 8，则需要1级

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

推荐答案

您还没有告诉我们如何知道用户是否拥有给定ID的权限。这是必要的信息。我将在下面放置一些代码，假设您向查询中添加了一个名为 hasRights 的列，并且如果用户没有权限，则该列的值为零；如果用户没有权限，则该列的值为1。做。您可能需要进行调整，因为我没有可以测试的数据，但希望它能使您接近。

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.

基本上，查询已更改为仅在用户具有权限的情况下才向级别添加1。如果用户具有权限，它也只会添加到排序路径，否则会附加一个空字符串。因此，如果ID 8和ID 9是用户只能访问的项目，则应该看到级别1和2，并对路径进行类似于 5/8/9而不是 5/6/8/9的排序。如果您仍然无法使它正常工作，那么如果您在SqlFiddle上发布示例架构，这将对我们有很大帮助。

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

这篇关于具有层次结构级别的递归CTE SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！