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

Question

我正在使用祖先gem来帮助组织我的应用程序在数据库中的树形结构。它基本上将孩子的祖先信息写入名为“祖先”的特殊列中。特定子代的祖先列可能类似于' 1 / 34 /87‘，其中该子代的父代是87，然后87的父代是34，34的父代是1。

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

我希望能够找到它们的祖先都没有隐藏的所有项目。我尝试使用REPLACE命令将斜杠转换为逗号，但IN需要一组逗号分隔的整数，而不是一个带有逗号分隔的字符串数字的字符串。

这很有趣，因为我可以在两个步骤中完成这个查询，例如，检索行，然后获取它的祖先列，拆分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应用程序。

Answer 1

如果您坚持不使用存储/过程，为什么不从物化路径方法切换到nested sets呢？

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

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

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

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

1. 打开一个cursor/recordset/whatever-row-based-approach-is-called-in-your-framework并将其传递给树控件，以便从数据库中更新和获取的数量与树上的操作(使分支可见、更新可见性、隐藏分支等)相关。在这种情况下(取决于框架)，您不必预先选择适当的元素(也不必在用户每次关闭或打开branch).
2. update时都发出select语句，以确保所有子项在数据库中的可见性。您似乎只在展开/折叠的节点上更新可见性(如果您需要保留折叠的分支和叶子的可见性，那么您可能有两个字段；这并不优雅，但我再次测试此选项，too)
3. investigate嵌套集；使用嵌套集所需的查询可能会更快。此外，编写SQL也变得更容易了(这里的SQL返回所有父节点都可见的节点，假设可见性为tinyint(1)；BIT_AND将在单个查询中对所有父节点执行聚合和)

选择node.name作为名称

从t1 AS节点，

t1作为父级

其中node.visibility =1，node.lft介于parent.lft和parent.rgt之间

按node.name分组

使BIT_AND(parent.visibility) =1

按node.lft 排序

(这是经过测试的，我以here为例，并添加了可见性)

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

Answer 2

您可能要做的是创建一个拆分函数(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