设计思路-关于树节点结构设计

文章主目录

接入redisSerch

增加path字段存储树的路径

1.可以通过当前节点追溯到上级所有父节点

2.可以通过当前节点查询所有子节点

比如满足以下需求

参考sql

------------------------------所有设备权限------------------------------
1.获得节点
 explain SELECT `id`,
       `userid`,
       `name`,
       `bgcolor`,
       `remotenum`,
       `orderid`,
       `pid`,
       `path`,
       `depth`,
       `tag_type`
FROM `re_tag`
WHERE userid = 12532915
  AND pid = 0
ORDER BY re_tag.tag_type desc, case when orderid > 0 then orderid else id end asc

2.获得数量 count位动态生成,前端目前分页是80一页
SELECT COUNT(distinct CASE
                          WHEN re_tag.path REGEXP '[[:<:]]317[[:>:]]' OR re_remotetag.tagid = 317
                              THEN re_remotetag.remoteid END) AS count_317,
       COUNT(distinct CASE
                          WHEN re_tag.path REGEXP '[[:<:]]134863[[:>:]]' OR re_remotetag.tagid = 134863
                              THEN re_remotetag.remoteid END) AS count_134863
FROM `re_remotetag`
         inner JOIN `re_tag` ON re_remotetag.tagid = re_tag.id
WHERE re_tag.userid = 12532915

3.是否有下级节点
explain SELECT pid as id, COUNT(1) as count
FROM `re_tag`
WHERE userid = 12532915
  AND pid IN (12532915,317,134863,142473,148154,171334)
GROUP BY pid
4.获取某个节点子节点
ent_tag.id IN (?) 为节点自身  (ent_tag.path REGEXP ?)) 为路径包含
SELECT distinct CASE WHEN ent_tag.path REGEXP '[[:<:]]55840[[:>:]]' OR ent_tag.id = 55840 THEN ent_tag.id END as tag_id
FROM `ent_tag`
WHERE ent_tag.ent_userid = ?
  AND (ent_tag.id IN (?) OR (ent_tag.path REGEXP ?))

------------------------------含有分组和设备场景------------------------------
1.获得节点  re_tag.id为授权的节点,path 也为授权节点拼接 id in为所有授权节点的父节点拼接 分页参数前端传80
explain SELECT `id`,
       `userid`,
       `name`,
       `bgcolor`,
       `remotenum`,
       `orderid`,
       `pid`,
       `path`,
       `depth`,
       `tag_type`
FROM `re_tag`
WHERE userid = 12532915
  AND pid = 0
  AND (((re_tag.id IN (134864,142743) OR (re_tag.path REGEXP '[[:<:]]134864|142473[[:>:]]'))) OR (id IN (134863, 171337,19858496,19858497)))
ORDER BY re_tag.tag_type desc, case when orderid > 0 then orderid else id end asc
LIMIT 100

2.获取数量场景
  re_tag.id为授权节点数量 regexp为授权节点动态生成 remoteid为直接授权主机的ids
explain SELECT COUNT(distinct CASE
                          WHEN re_tag.path REGEXP '-317-' OR re_remotetag.tagid = 317
                              THEN re_remotetag.remoteid END) AS count_317,
       COUNT(distinct CASE
                          WHEN re_tag.path REGEXP '[[:<:]]134863[[:>:]]' OR re_remotetag.tagid = 134863
                              THEN re_remotetag.remoteid END) AS count_134863,
       COUNT(distinct CASE
                          WHEN re_tag.path REGEXP '[[:<:]]142473[[:>:]]' OR re_remotetag.tagid = 142473
                              THEN re_remotetag.remoteid END) AS count_142473,
       COUNT(distinct CASE
                          WHEN re_tag.path REGEXP '[[:<:]]171337[[:>:]]' OR re_remotetag.tagid = 171337
                              THEN re_remotetag.remoteid END) AS count_171337
FROM `re_remotetag`
         inner JOIN `re_tag` ON re_remotetag.tagid = re_tag.id
WHERE re_tag.userid = 12532915
  AND (re_tag.id IN (134864,142473 ) OR (re_tag.path REGEXP ' [[:<:]]134864|142473[[:>:]]') OR re_remotetag.remoteid IN (994475629))

3.是否含有子节点
pid为查询的pid id为授权节点的id集合
explain SELECT pid as id, COUNT(1) as count
FROM `re_tag`
WHERE ((userid = 12532915 AND pid IN (317,134863,142473)) OR (id IN (171337,134863,134863,171337,19858496,19858497,134864,142473)))
GROUP BY pid

4.获取指定节点子节点
regepx为查询父节点id,re_tag.id IN (?, ?)为授权节点id re_tag.path REGEXP ? 为授权节表达式  re_tag.id IN 为授权主机带过来的
SELECT distinct CASE WHEN re_tag.path REGEXP '[[:<:]]134863[[:>:]]' OR re_tag.id = 134863 THEN re_tag.id END as tag_id
FROM `re_tag`
WHERE re_tag.userid = ?
  AND (re_tag.id IN (?, ?) OR (re_tag.path REGEXP ?) OR re_tag.id IN (?))

mysql regexp不支持此写法后面改成 path字段例子:1-2,33-31

CONCAT('-',ent_tag.path, '-') REGEXP ? -22-|-333-

回到顶部

接入redisSerch

几种授权场景
1.设备直接授权人(授权人可以看到设备)
2.设备授权部门(如授权部门A,A部门及以下部门用户都可以看到数据)
3.分组授权人 (授权人可以直接查看分组及分组以下数据)
4.分组授权部门(如授权分组A,A部门及以下部门可以看到分组以下数据)

---------------设备索引----------------------
设备id      授权人TAGS      授权部门[TAGS]向下的一棵树(如授权A)      设备所属分组树,向上的一颗树(比如A-B-C) 设备存在C组以下tag存储
 1          [1,2,3,4]              [A-B-C-B1-C1]                           [A,B,C]
ps:向下的一棵树会存储大量id，部门一般不会多

需要动态维护的场景
1.设备授权人 动态维护tags,新增授权add 移除授权remote
2.设备分组更改 动态维护设备分组TAGS树,如将设备分组改为B 则存储[A,B]
3.部门层级发生改变需要维护TAG树
4.分组层级发生改变,需要维护TAG树

如何查询当前用户能看到设备
1.查询当前登录用户授权分组+查询当前登录用户部门授权分组=$权限范围内分组
2.获取用户所属部门
授权人TAGS:{currentUserID} or 设备所属分组树:$权限范围内分组 or 授权部门树:${当前用户所属部门数组}