I have two tables jerarquia
and permisos
, where hierarchy has an ID, an ID_PARENT (pointing to the same table towards a parent node) and a DESCRIPTION of the node itself. The permissions table has an ID_OPERATOR (id of the user who is logged in the system), ID_NODE (id of the node for which I am specifying permissions, foreign to the table jerarquia
) and R (which is a BIT that indicates if it has read permissions ), not all nodes have a specified permission, there may be nodes that do not appear in the permissions table
I'm doing a query that brings you the nodes that match a criteria and iteratively brings the top nodes of each branch, that algorithm worked fine for me, but now that the permissions table has been added if a node has R = 0 (ie , which has read permissions denied) the entire branch hanging from it should not be seen. So far I have this query, but the truth is that I am quite stuck:
SET NOCOUNT ON
DECLARE @TablaFiltrada TABLE (
ID INT,
ID_PADRE INT,
DESCRIPCION NVARCHAR(200),
R BIT
)
DECLARE @Rows INT
INSERT INTO @TablaFiltrada (ID, ID_PADRE, DESCRIPCION, R)
SELECT J1.ID, ID_PADRE, DESCRIPCION, P.R
FROM jerarquia J1
LEFT JOIN (SELECT * FROM PERMISOS WHERE ID_OPERADOR = 1002) P ON J1.ID = P.ID_NODO
WHERE DESCRIPCION LIKE 'keyword' AND (R IS NULL OR R = 1)
SELECT @Rows = @@ROWCOUNT
WHILE @Rows <> 0
BEGIN
INSERT INTO @TablaFiltrada (ID, ID_PADRE, DESCRIPCION, R)
SELECT P2.ID, P2.ID_PADRE, P2.DESCRIPCION, P.R
FROM jerarquia P2
INNER JOIN @TablaFiltrada P1 ON P2.ID = P1.ID_PADRE
LEFT JOIN @TablaFiltrada P3 ON P3.ID = P2.ID
LEFT JOIN (SELECT * FROM PERMISOS WHERE ID_OPERADOR = 1002) P ON P2.ID = P.ID_NODO
WHERE P3.ID IS NULL
SELECT @Rows = @@ROWCOUNT -- Sigo iterando
END
SELECT DISTINCT * FROM @TablaFiltrada
WHERE R IS NULL OR R = 1
ORDER BY DESCRIPCION
From already thank you very much!
If you doubt, as Lamak says it is an interesting question, I will tell you a way to solve it, it may not be the most optimal and it has some limitations:
LEN(VARCHAR(MAX)) / 4
Suppose we have a complete Hierarchy like the following:
It is a silly example of categories to classify a product, now suppose we want to "disable" the entire "Cell Phones" branch, that is "Cell Phones", "High End", "Iphone 6", etc but only disabling the "Cell Phones" level ".
The exit:
The gist of this method is to generate a field
nodo
that is the hierarchical representation of the "tree", something like this:This makes it clearer than if we only have the
ID=2
as disabled, just updating the records whoseLEFT(nodo,LEN(nodo)) = '0002'
we will be disabling the entire branch. We can modify the way of creating the nodes, I personally like to see it that way because it is useful for ordering, but the nodes could be created by concatenating eachid
, for example,2|9|11
and we would have more flexibility in terms of their number.There I found a way that I don't know if it's the best possible, but it works well: