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

Question

我有多个类别，其中可以没有一个或一个或多个子类别。在

从理论上讲，这个过程是无限的。所以，就像有多棵树。在

树的例子。在

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

我也有物品。一个项目可以是多个类别。在

此时，为了连接类别，我在数据库中使用了三个字段：

• children（某个类别的子项），

• 路径（[1,4,8]，基本上是祖父母、父母和类别本身的ID）

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

使用这些字段，我避免了一些递归查询和使用更多的查询。在

我通常检索以下数据：

• 顶级类别（深度0）

• 类别的子类别

• 同级类别

• 类别中的项目（例如祖父母类别，将显示其直接项目、子项目和孙子项目）

现在我使用的是Django（希望转到FastAPI）和PostgreSQL，每次对类别执行CRUD操作时，三个字段（path、depth、children）都会被修改。在

我在想也许是一个更好的方法，来维护/检索类别树和相应的条目。在

Answer 1

使用递归CTE查询生成层次结构树。根据您的层次结构大小和典型查询，索引和自动缓存可能足以使这一过程足够快。否则，物化视图可能是一个很好的方法。在

如果需要，可以选择使用单独的顶层节点，或者让顶层节点的父节点为空。有几个像TOP这样的节点可以在同一个表中有多个树。此外，对单个下游节点和向上的节点进行查询应该不难。在

DROP TABLE IF EXISTS category;

CREATE TABLE category (
    id varchar PRIMARY KEY,
    parent varchar
);

COPY category (id,parent)
FROM  stdin WITH DELIMITER ';';
TOP;\N
1;TOP
2;TOP
1A;1
1B;1
1A1;1A
1A2;1A
\.

WITH RECURSIVE tree AS (
  SELECT
    id,
    parent,
    id  AS path
  FROM
    category
  WHERE
    parent IS NULL
UNION
  SELECT
    c.id,
    c.parent,
    p.path || ' -> ' || c.id
  FROM
    category c
  INNER JOIN
    tree p
   ON c.parent = p.id
  )

SELECT * FROM tree
ORDER BY path;

Answer 2

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

在阵列中存储完整路径就是其中之一。但是这种解决方案很难实施引用完整性（如何保证数组中的id在表中确实存在？），并且简单的树操作是乏味的（如何枚举给定节点的直接子节点？）。在

@vesakarjalinen的回答建议使用邻接列表模型，这是一个单独的表，其中每个元素都指其直系祖先。它可以工作，但也有缺点：通常，遍历层次结构非常复杂（比如获取给定节点的所有子节点或父节点）：为此需要某种迭代或递归，而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)
);

对于您提供的树结构：

^{pr2}$

您将存储以下数据：

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

现在，假设您要检索给定类别的所有子级，这很简单：

^{4}$

要获取所有父对象，只需将where parent_id = ...替换为where child_id = ...。在

您可以使用join引入主表：

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

Answer 3

如果您计划在您的项目中坚持使用django，并且想要一些更“开箱即用”的东西，那么您应该看看django-treebeard。这在需要数据库中树结构的大型python项目中使用，比如Wagtail。在