如何基于PostgreSQL递归自关联Tag模型高效构建三级层级结构的QuerySet

嘿，这个三级标签层级的问题我之前做项目时刚好碰到过，咱们来捋清楚怎么高效解决它～

首先得说你当前代码的核心问题：用ArrayAgg('tags__tags__slug')会把所有顶级标签下的二级标签对应的三级标签都堆进一个数组里，完全没法对应到具体的二级标签上——就像把所有孩子的玩具都混在一个箱子里，你分不清哪个玩具属于哪个孩子。

要解决这个问题，关键是要把二级标签和它对应的三级标签绑定在一起，而且要尽量在数据库层面完成聚合（避免Python层面的二次处理，最大化效率）。PostgreSQL的JSON函数刚好能帮我们做到这一点，结合Django的Subquery和ArrayAgg就能实现一次查询搞定三级结构。

高效解决方案代码 #

from django.db.models import ArrayAgg, Subquery, OuterRef, Func, F

# 第一步：定义子查询，用来获取每个二级标签对应的三级标签slug列表
level2_subquery = Tag.objects.filter(
    tag__slug=OuterRef('slug')  # 关联外层查询的二级标签slug
).values_list('slug', flat=True)

# 第二步：查询顶级标签，同时聚合出绑定了三级标签的二级标签列表
tag_hierarchy = Tag.objects.filter(tag=None).annotate(
    # 用PostgreSQL的json_build_object把二级标签的slug和它的三级列表打包成JSON对象
    level1_with_level2=ArrayAgg(
        Func(
            F('tags__slug'),  # 二级标签的slug
            Subquery(level2_subquery),  # 对应的三级标签slug数组
            function='json_build_object',
            template='%(function)s(\'slug\', %(expressions[0])s, \'level2\', %(expressions[1])s)'
        )
    )
).values('slug', 'level1_with_level2')

怎么在模板里用 #

返回的tag_hierarchy结构是这样的（每个顶级标签对应一条数据）：

[
    {
        "slug": "礼物",
        "level1_with_level2": [
            {"slug": "新年礼物", "level2": ["贺卡", "玩具"]},
            {"slug": "生日礼物", "level2": []}
        ]
    }
]

模板里直接遍历就行，完全不用额外处理：

{% for level0 in tag_hierarchy %}
  <div class="level0-tag">{{ level0.slug }}</div>
  {% for level1 in level0.level1_with_level2 %}
    <div class="level1-tag">{{ level1.slug }}</div>
    {% for level2_slug in level1.level2 %}
      <div class="level2-tag">{{ level2_slug }}</div>
    {% endfor %}
  {% endfor %}
{% endfor %}

为什么这个方案高效 #

1. 单查询完成所有操作：所有聚合和关联都在数据库层面通过JOIN和JSON函数完成，没有N+1查询，也不需要prefetch_related之后在Python层面重组数据。
2. 最小化CPU负载：数据库做聚合比Python处理更快，而且返回给应用的数据已经是模板需要的嵌套结构，应用层只需要直接传递给模板即可。
3. 利用PostgreSQL特性：json_build_object和ArrayAgg都是PostgreSQL原生支持的高效函数，性能拉满。

补充说明 #

你原来的代码里写了values('name'，应该是笔误，你的模型里只有slug字段，记得改成slug哦～

内容的提问来源于stack exchange，提问作者0leg