代码之家 › 专栏 › 技术社区 › Brendon Muir

检查基于“祖先”的SQL表中的继承属性

tree ruby-on-rails mysql sql

Brendon Muir · 技术社区 · 14 年前

我正在使用祖传的gem帮助在数据库中组织我的应用程序的树结构。它基本上是将child的祖先信息写入一个名为“祖先”的特殊列。特定子级的祖先列可能看起来像“1/34/87”,其中该子级的父级为87,然后87的父级为34,34的父级为1。

似乎我们可以从这个表中选择行,每个行都有一个子查询,检查所有的祖先,看看它是否设置了某个属性。例如,在我的应用程序中,只需将父元素的可见性列设置为0,就可以隐藏项及其子项。

我想能找到所有他们祖先都不藏的东西。我试着用replace命令将斜杠转换成逗号,但是需要一组逗号分隔的整数,而不是一个逗号分隔的字符串。

这很有趣,因为我可以用两个步骤来完成这个查询,例如检索行,然后取它的祖先列,分割出id,然后进行另一个查询,检查id是否在该id集中,可见性永远不是0等等!但将这些合并到一个查询中似乎是一项相当艰巨的任务。大量的搜索显示了一些答案,但没有一个真正做我想要的。

SELECT * FROM t1 WHERE id = 99;

99的祖先栏写着“1/34/87”

SELECT * FROM t1 WHERE visibility = 0 AND id IN (1,34,87);

有点向后,但如果这不返回行,则该项是可见的。

以前有没有人遇到过这个问题并提出解决方案。我真的不想走存储过程路线。这是一个rails应用程序。

2 回复 | 直到 14 年前

Unreason 14 年前

如果您坚持不使用存储/过程,为什么不切换到 nested sets 从你的物质化道路上?

否则,从应用程序端执行这两个查询(或使用astander建议的存储过程)。

SQL中层次结构的良好链接 here

编辑: 似乎您正在数据库中存储有关树控件状态的信息。

假设这确实是合理的,并且您需要将可见性存储在数据库中,您可能会调查以下场景(这些是想法,而不是直接的解决方案):

打开框架中调用的游标/记录集/任何基于行的方法,并将其传递给树控件,以便数据库中的更新和获取次数与树上的操作相关(使分支可见、更新可见性、隐藏分支等)。在这种情况下(取决于框架),不必预先选择适当的元素(也不必每次用户关闭或打开分支时发出select语句)。
更新数据库中所有子项的可见性。似乎您只在展开/折叠的节点上更新可见性(如果您需要保留折叠的分支和叶的可见性,那么您可能有两个字段;这并不优雅,但我也将测试此选项)
再次调查嵌套集;使用嵌套集时,所需查询可能会更快。此外,编写sql也变得容易一些(这里的sql返回所有父节点都可见的节点,假设可见性为tinyint(1);bit_将在单个查询中对所有父节点进行聚合)

选择node.name作为名称
从T1开始作为节点,
T1作为亲本
其中node.visibility=1,node.lft介于parent.lft和parent.rgt之间
按node.name分组
具有位和(父可见性)=1
按节点排序.lft

(这是经过测试的,我从 here ,并添加可见性)

另外,在测试和基准测试每个解决方案时,不要忘记基准测试所有操作(选择可见分支、打开隐藏分支、将节点标记为不可见等)。

Adriaan Stander 14 年前

如果您还没有split函数,那么您可能需要创建一个split函数( Split a Delimited String in SQL )然后将其用作in选择。

还有另一种方法,但它可能会降低大型表的性能。有点像

SELECT  *
FROM    Table t INNER JOIN
        Table tParents      
        ON  (   t.Path LIKE CAST(tParents.ID AS VARCHAR(20)) + '/%'
                OR  t.Path LIKE +'%/' + CAST(tParents.ID AS VARCHAR(20)) + '/%'
                OR  t.Path LIKE +'%/' + CAST(tParents.ID AS VARCHAR(20)))

WHERE   t.ID = 99
AND     tParents.Visible = 0