Django ORM结合select_related、prefetch_related实现递归家谱查询

前置模型定义 #

首先基于你提到的三类模型给出参考关联定义，可根据实际业务字段调整：

from django.db import models

class Grandpa(models.Model):
    name = models.CharField(max_length=50)
    age = models.IntegerField()
    # 其余业务字段自定义
    class Meta:
        db_table = "grandpa"

class Father(models.Model):
    name = models.CharField(max_length=50)
    age = models.IntegerField()
    grandpa = models.ForeignKey(Grandpa, on_delete=models.CASCADE, related_name="fathers")
    # 其余业务字段自定义
    class Meta:
        db_table = "father"

class Son(models.Model):
    name = models.CharField(max_length=50)
    age = models.IntegerField()
    father = models.ForeignKey(Father, on_delete=models.CASCADE, related_name="sons")
    # 其余业务字段自定义
    class Meta:
        db_table = "son"

方案1：固定三级结构专用实现（性能最优） #

基于Django ORM的Prefetch对象实现分层过滤，全程仅执行3次SQL查询，无N+1性能问题，优先推荐使用：

from django.db.models import Prefetch

def get_family_tree(grandpa_filters=None, father_filters=None, son_filters=None):
    # 初始化各层级默认过滤规则为空
    grandpa_filters = grandpa_filters or {}
    father_filters = father_filters or {}
    son_filters = son_filters or {}

    # 预定义Son层查询，应用Son级过滤规则
    son_queryset = Son.objects.filter(**son_filters)
    # 预定义Father层查询，应用Father级过滤规则，同时预加载符合条件的Son
    father_queryset = Father.objects.filter(**father_filters).prefetch_related(
        Prefetch("sons", queryset=son_queryset, to_attr="filtered_sons")
    )
    # 查询Grandpa层，应用Grandpa级过滤规则，同时预加载符合条件的Father
    grandpa_queryset = Grandpa.objects.filter(**grandpa_filters).prefetch_related(
        Prefetch("fathers", queryset=father_queryset, to_attr="filtered_fathers")
    )

    # 组装返回树形结构
    family_tree = []
    for grandpa in grandpa_queryset:
        grandpa_item = {
            "id": grandpa.id,
            "name": grandpa.name,
            "level": "grandpa",
            "children": []
        }
        for father in grandpa.filtered_fathers:
            father_item = {
                "id": father.id,
                "name": father.name,
                "level": "father",
                "children": []
            }
            for son in father.filtered_sons:
                son_item = {
                    "id": son.id,
                    "name": son.name,
                    "level": "son"
                }
                father_item["children"].append(son_item)
            grandpa_item["children"].append(father_item)
        family_tree.append(grandpa_item)
    return family_tree

调用示例 #

比如查询年龄大于70的爷爷、年龄在40-50岁之间的父亲、仍在上学的儿子（假设Son模型有is_student字段），调用方式如下：

tree = get_family_tree(
    grandpa_filters={"age__gt": 70},
    father_filters={"age__range": (40, 50)},
    son_filters={"is_student": True}
)

方案2：通用递归实现（适合层级扩展） #

如果后续可能新增更多家族层级，可使用递归实现，适配任意深度的树结构：

def get_recursive_node(person, level_filters, current_level=0):
    # 层级配置映射，后续新增层级直接在此处添加即可
    level_config = [
        {"model": Grandpa, "child_related": "fathers"},
        {"model": Father, "child_related": "sons"},
        {"model": Son, "child_related": None}
    ]
    # 组装当前节点数据
    node_data = {
        "id": person.id,
        "name": person.name,
        "level": level_config[current_level]["model"].__name__,
        "children": []
    }
    # 存在下一层级则递归查询
    child_related = level_config[current_level]["child_related"]
    if child_related:
        next_level = current_level + 1
        next_filter = level_filters.get(next_level, {})
        children = getattr(person, child_related).filter(**next_filter)
        for child in children:
            node_data["children"].append(get_recursive_node(child, level_filters, next_level))
    return node_data

def get_general_family_tree(level_filters):
    top_filter = level_filters.get(0, {})
    top_nodes = Grandpa.objects.filter(**top_filter)
    return [get_recursive_node(node, level_filters) for node in top_nodes]

调用示例 #

# 键为层级序号，0对应Grandpa、1对应Father、2对应Son
level_filters = {
    0: {"age__gt": 70},
    1: {"age__range": (40, 50)},
    2: {"is_student": True}
}
tree = get_general_family_tree(level_filters)

提示：两种方案均支持不传对应层级的过滤参数，默认返回该层级全量符合关联关系的节点。

内容的提问来源于stack exchange，提问作者Poula Adel