如何选择以下内容？

发布于 2024-10-20 21:33:48 字数 805 浏览 4 评论 0原文

我有一个表 DOCUMENTS 和一个表 FOLDERS

FOLDERS
ID
lft
rgt

DOCUMENTS
ID
folderID -> FOLDERS(ID)
title

所以，我们这里有一个任意深度的文件夹树（嵌套集）。每个文件夹可以包含任意数量的文档。

选择某个文件夹下的所有文档的最有效方法是什么？也就是说，我不仅要选择文件夹中的文档，还要选择该文件夹子文件夹中的文档。

编辑：示例数据以使事情更清楚：

FOLDERS
ID     LFT     RGT
1      1       10      
2      2       5
3      3       4
4      6       7
5      8       9

这表示像这样的文件夹结构...

       1
    2  4  5
 3

其中 2、4、5 是 1 的子级，3 是 2 的子级

现在...

假设 DOCUMENTS 有以下数据。 ..

DOCUMENTS
ID     FolderID    Title
1      5           Doc 1
2      3           Doc 2
3      2           Doc 3

给定文件夹的 ID，我想选择该文件夹中的所有文档以及该文件夹子文件夹中的所有文档。例如，给定folderID为2，SELECT将返回文档2和3

（提前）感谢您的帮助

原文

I have a table DOCUMENTS and a table FOLDERS

FOLDERS
ID
lft
rgt

DOCUMENTS
ID
folderID -> FOLDERS(ID)
title

So, what we have here is an arbitrarily deep tree of folders (nested-sets). Each folder can contain an arbitrary number of documents.

What is the most efficient way to SELECT all of the documents that are beneathe a certain folder? That is, I want to SELECT not only the documents that are in a folder, but the documents that are in that folder's subfolders.

Edit: Sample data to make things more clear:

FOLDERS
ID     LFT     RGT
1      1       10      
2      2       5
3      3       4
4      6       7
5      8       9

This represents a folder structure like so...

       1
    2  4  5
 3

Where 2, 4, 5 are children of 1, and 3 is a child of 2

Now...

Let's say DOCUMENTS has the following data...

DOCUMENTS
ID     FolderID    Title
1      5           Doc 1
2      3           Doc 2
3      2           Doc 3

Given the ID of a folder, I want to SELECT all of the documents in that folder, and all of the documents in that folder's subfolders. So for example given a folderID of 2, the SELECT would return documents 2 and 3

Thanks (in advance) for your help

分享到QQ

分享到微博

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

发布评论

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

薯片软お妹 2024-10-27 21:33:48

首先，您需要从文件夹表中获取 lft 和 rgt 值：

select lft, rgt from folders where id = 2;

...然后使用这些值来识别该节点或其子节点中存在的文档：

select d.* from documents d left join folders f on f.id = d.folderID where f.lft >= 2 and f.rgt <= 5;

您可以在代码中单独调用这两个查询，也可以将它们与子查询组合起来，或者编写一个组合它们的存储过程。

First, you'll want to get the lft and rgt values from the folders table:

select lft, rgt from folders where id = 2;

...then use those to identify documents that exist within that node or its children:

select d.* from documents d left join folders f on f.id = d.folderID where f.lft >= 2 and f.rgt <= 5;

You can call these two queries separately in your code, you could combine them with subqueries, or you could write a stored procedure that combines them.

回复收藏 0 原文

~没有更多了~