嵌套集过滤

发布于 2024-09-25 21:43:36 字数 483 浏览 0 评论 0原文

我正在为我的菜单树使用嵌套集模型，并且我正在尝试通过一些过滤来获取节点。我有几个根节点。示例：

Menu1(on)
 \-Submenu1(on)
 \-Submenu2(on)
Menu2(off)
 \-Submenu3(on)
 \-Submenu4(on)
    \-Submenu5(on)
Menu3(on)

我想返回所有“打开”的节点，但不返回父节点“关闭”的节点。对于上面的示例，查询应仅返回 Menu1（及其子项）和 Menu3。

Menu1(on)
 \-Submenu1(on)
 \-Submenu2(on)
Menu2(on)
 \-Submenu3(on)
 \-Submenu4(off)
    \-Submenu5(on)
Menu3(on)

对于此示例，查询应返回除 Submenu4 及其子菜单之外的所有菜单。

有什么想法吗？

提前致谢。

原文

I'm using nested set model for my menu tree, and I'm trying to get nodes with some filtering. I have several root nodes.
Example:

Menu1(on)
 \-Submenu1(on)
 \-Submenu2(on)
Menu2(off)
 \-Submenu3(on)
 \-Submenu4(on)
    \-Submenu5(on)
Menu3(on)

I want to return all nodes "on" but not the ones that have parents "off".
The query, for the example above, should return only Menu1 (and children) and Menu3.

Menu1(on)
 \-Submenu1(on)
 \-Submenu2(on)
Menu2(on)
 \-Submenu3(on)
 \-Submenu4(off)
    \-Submenu5(on)
Menu3(on)

For this example, the query should return all except Submenu4 and it's children.

Any ideas?

Thanks in advance.

分享到QQ

分享到微博

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

发布评论

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

幸福还没到 2024-10-02 21:43:36

SELECT t0.*
FROM sometable AS t0
LEFT JOIN sometable AS t1 ON t0.lft BETWEEN t1.lft AND t1.rgt AND t1.active='off'
WHERE t1.lft IS NULL
ORDER BY t0.lft

也就是说，选择不存在包含目标行（或与目标行相同的行）的 off 行的每一行。

SELECT t0.*
FROM sometable AS t0
LEFT JOIN sometable AS t1 ON t0.lft BETWEEN t1.lft AND t1.rgt AND t1.active='off'
WHERE t1.lft IS NULL
ORDER BY t0.lft

That is, select each row where there is no row with off that contains (or is the same row as) the target row.

回复收藏 0 原文

~没有更多了~