python - 使用 Python 和 PostgreSQL 管理多个类别树

Question

我有多个类别，这些类别可以没有，也可以有一个或多个子类别。

这个过程理论上可以达到无限。所以，这就像拥有多棵树。

树示例。

A
 - A1
     - A11
     - A12
-A2
B
C
 - C1

我还有元素。一个项目可以属于多个类别。

此时要连接类别，在数据库中我使用三个字段:

• 子项(类别的子项)，

• path([1,4,8]，基本上是祖 parent 、 parent 和类别本身的id)

• 深度，表示每个类别在树中的级别

使用此字段，我可以避免一些递归和使用更多查询。

我通常检索如下数据:

• 顶级类别(深度 0)

• 类别的子类别

• 同级类别

• 类别中的项目(例如祖 parent 类别，将显示其直接项目、子项目和孙项目)

目前我正在使用Django(想迁移到FastAPI)和PostgreSQL，每次对类别进行CRUD操作时，这三个字段(路径，深度，子项)都会被修改。

我认为也许是维护/检索类别树和相应项目的更好方法。

Answer 1

有多种可能的策略可以将树存储在数据库中。

将当前的完整路径存储在数组中就是其中之一。但这种解决方案很难强制执行引用完整性(如何保证数组中的这些 id 确实存在于表中？)，并且简单的树操作很繁琐(如何枚举给定节点的直接子节点？)。

@VesaKarjalainen 的答案建议使用邻接列表模型，这是一个单独的表，其中每个元素都引用其直接祖先。它可以工作，但也有缺点:通常，遍历层次结构很复杂(例如获取给定节点的所有子节点或父节点):为此，您需要某种迭代或递归，这需要 SQL 引擎做事效率不高。

我建议使用闭包表方法。这是通过创建一个单独的表来存储树中所有可能的路径来实现的，如下所示:

create table category_path (
    parent_id int,
    child_id int,
    level int,
    primary key(parent_id, child_id),
    foreign key(parent_id) references category(id),
    foreign key(parent_id) references category(id)
);

对于您提供的树结构:

        A       B     C 
       / \            |
     A1   A2          C1
     /\
  A11  A12

您将存储以下数据:

parent_id    child_id    level
A            A           0
A            A1          1
A            A2          1
A            A11         2
A            A12         2
A1           A11         1
A1           A12         1
B            B           0
C            C           0
C            C1          1

现在，假设您想要检索给定类别的所有子级，就像这样简单:

select * from category_path where parent_id = 'A'

要获取所有父项，只需将 where child_id = ... 替换为 where child_id = ... 即可。

您可以使用连接引入主表:

select c.*
from category_path cp
inner join categories c on c.id = cp.parent_id
where cp.parent_id = 'A'