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

发布于 2024-08-28 01:34:12 字数 721 浏览 2 评论 0原文

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

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

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

这很有趣，因为我可以分两步执行此查询，例如检索行，然后获取其祖先列，拆分 id 并进行另一个查询，检查 id 是否在该 id 集合中，并且可见性不存在0 哇啦！但将这些连接到一个查询中似乎是一项艰巨的任务。大量搜索显示了一些答案，但没有一个真正符合我的要求。

SELECT * FROM t1 WHERE id = 99;

99 的祖先列

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

向后读取“1/34/87”，但如果这不返回任何行，则该项目是可见的。

有没有人遇到过这个问题并提出解决方案。我真的不想走存储过程路线。它适用于 Rails 应用程序。

原文

I'm using the ancestry gem to help organise my app's tree structure in the database. It basically writes a childs ancestor information to a special column called 'ancestry'. The ancestry column for a particular child might look like '1/34/87' where the parent of this child is 87, and then 87's parent is 34 and 34's is 1.

It seems possible that we could select rows from this table each with a subquery that checks all the ancestors to see if a certain attribute it set. E.g. in my app you can hide an item and its children just by setting the parent element's visibility column to 0.

I want to be able to find all the items where none of their ancestors are hidden. I tried converting the slashes to comma's with the REPLACE command but IN required a set of comma separated integers rather than one string with comma separated string numbers.

It's funny, because I can do this query in two steps, e.g. retrieve the row, then take its ancestry column, split out the id's and make another query that checks that the id is IN that set of id's and that visibility isn't ever 0 and whala! But joining these into one query seems to be quite a task. Much searching has shown a few answers but none really do what I want.

SELECT * FROM t1 WHERE id = 99;

99's ancestry column reads '1/34/87'

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

kind of backwards, but if this returns no rows then the item is visible.

Has anyone come across this before and come up with a solution. I don't really want to go the stored procedure route. It's for a rails app.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

多谢你的绝情让我学会死心 2024-09-04 01:34:12

您可能想要做的是创建一个 Split 函数，如果您还没有一个 (在 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

What you might want to do is create a Split function, if you do not already have one (Split a Delimited String in SQL ) and then use that as the IN selection.

There is also another way, but it might degrade performance on large tables.
Something like

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

回复收藏 0 原文

我们只是彼此的过ke 2024-09-04 01:34:12

如果您坚持摆脱存储/过程为什么不切换到嵌套集< /a> 来自您的物化路径方法？

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

SQL 中层次结构的良好链接此处

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

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

打开游标/记录集/任何基于行的方法-在您的框架中调用并将其传递给树控件，以便从数据库中更新和获取的数量与树上的操作相关（使分支可见、更新可见性、隐藏分支等）。在这种情况下（取决于框架），您不必预先选择适当的元素（也不必在每次用户关闭或打开分支时发出选择语句）。
更新数据库中所有子项的可见性。看来您仅在展开/折叠的节点上更新可见性（如果您需要保留折叠的分支和叶子的可见性，那么您可能有两个字段；这并不优雅，但我也会测试此选项）< /p>
< p>再次研究嵌套集合；使用嵌套集所需的查询可能会变得更快。此外，编写 SQL 也变得更加容易（这里的 SQL 返回所有父级都可见的节点，假设可见性为tinyint(1)；BIT_AND 将在单个查询中对所有父级进行聚合 AND）
SELECT 节点.name AS 名称
FROM t1 AS 节点，
t1 AS 父级
WHERE node.visibility = 1 AND node.lft 位于parent.lft 和parent.rgt 之间
GROUP BY 节点名称
HAVING BIT_AND(parent.visibility) = 1
ORDER BY node.lft

（这是经过测试的，我从这里举了例子，并增加了可见性）

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

If you insist on getting away from stored/procedural why not switch to nested sets from your materialized paths approach?

Otherwise, do the two queries from the application side (or use stored procedure as suggested by astander).

Good links for hierarchies in SQL here

EDIT:
It seems that you are storing information about the state of the tree control in the database.

Assuming that this is really justified and that you need to store the visibility in the database you might investigate the following scenarios (these are ideas, not immediate solutions):

open a cursor/recordset/whatever-row-based-approach-is-called-in-your-framework and pass that to the tree control so that the number of updates and fetches from the database are related to the actions on the tree (making branches visible, updating visibility, hiding branches, etc). In this case (depending on the framework) you don't have to preselect the proper elements (nor issue select statement every time user closes or opens a branch).
update the visibility in the database for all children. it seems that you are updating visibility only on a node that is expanded/collapsed (if you need to preserve visibility of collapsed branches and leafs then you might have two fields; this is not elegant, but I would test this option, too)
investigate nested sets again; with nested sets required queries might become faster. Also it becomes a bit easier to write SQL (here's SQL that returns nodes that have all parents visible, assuming visibility is tinyint(1); BIT_AND will do aggregate AND on all the parents in a single query)
SELECT node.name AS name
FROM t1 AS node,
t1 AS parent
WHERE node.visibility = 1 AND node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
HAVING BIT_AND(parent.visibility) = 1
ORDER BY node.lft

(this is tested, I took example from here, and added visibility)

Also, when you test and benchmark each solution do not forget to benchmark all the operations (selecting visible branches, opening hidden branches, marking node invisible, etc..).

回复收藏 0 原文

~没有更多了~