Django的递归查询集 [英] Recursive QuerySet with django

问题描述

我有一个引用此模型的自身以允许构建树：

I have this model referencing itself to allow building a tree:

class PartCategory(models.Model):
    parent = models.ForeignKey('PartCategory', on_delete=models.DO_NOTHING, null=True, default=None, blank=True)
    name = models.TextField()

我现在有一个SQL查询，可以一次选择一个元素及其所有子元素（在本例中为id = 64的元素）： / p>

I now have an SQL query to get one element and all of its child in one select (in this example the element with id=64):

WITH RECURSIVE
  under_partcategory(id,name, parent_id,level) AS (
    select api_partcategory.id,api_partcategory.name,api_partcategory.parent_id,0 from api_partcategory where api_partcategory.id=64
    UNION ALL
    SELECT api_partcategory.id,api_partcategory.name,api_partcategory.parent_id, under_partcategory.level+1
    FROM api_partcategory JOIN under_partcategory ON api_partcategory.parent_id=under_partcategory.id
    ORDER BY 2
  )
SELECT * FROM under_partcategory;

我正在寻找一种在QuerySet中表示此查询的方法，以允许从中添加过滤选项和字段构造我的模型，但是我不太清楚这是否可行。

I am searching a way to express this query inside a QuerySet to allow adding filtering options and fields construction from my model but I don't quite know if it's kind of possible.

我可以构造一个由我的模型构建的原始查询构成的复合QuerySet，并在同时允许使用过滤器和 order_by 功能？

Can I construct a composite QuerySet made from some kind of raw query built from my model, and at the same time allowing to use the filter and order_by capabilities?

编辑：

更具体地说，我想要一个QuerySet，我可以使用这种方式：

To be more specific I would like to have a QuerySet I can use this whay:

PartCategory.recursive.filter(64)

内部将通过用PartCategory模型中的元素替换所有包含的零件来构建请求：

Internally it would build the request by replacing all embraced parts with elements from PartCategory model:

WITH RECURSIVE
  under_{model name}({model fields},level) AS (
    select {model fields},0 from {model name} {where {model name}.id=64 #extracted from filter}
    UNION ALL
    SELECT {model fields}, under_{model_name}.level+1
    FROM {model name} JOIN under_{model_name} ON {model_name}.parent_id=under_{model_name}.id
    ORDER BY 2
  )
SELECT * FROM under_{model_name};

我正在与自定义经理一起玩，尝试构建它，但现在我不知道如何从模型中构建 {model字段} 以及如何返回符合 filter 资格的对象。

I'm playing with a custom managers to try to build this but for now I don't know how to build {model fields} from my model and how to return an object eligible for filter.

编辑2：

正如colwin所说，我使用 django-mptt ，但与我最初想的并不完全一样。

As stated by colwin I made it with django-mptt but not exactly as I was thinking in first place.

我的模型带有 django-mptt 变为：

from mptt.models import MPTTModel, TreeForeignKey

class PartCategory(MPTTModel):
    parent = TreeForeignKey('self', null=True, blank=True, related_name='children', db_index=True)
    name = models.TextField()
    class MPTTMeta:
        order_insertion_by = ['name']

并提取所有项目64孩子们，我现在要做的事：

And to extract my item 64 with all its childrens I now do:

categories = PartCategory.objects.get(id=64).get_descendants(include_self=True)

这很简单！

推荐答案

这不适合您吗？

parts = PartCategory.objects.raw('''
    WITH RECURSIVE
    under_partcategory(id,name, parent_id,level) AS (
    select   api_partcategory.id,api_partcategory.name,api_partcategory.parent_id,0 from api_partcategory where api_partcategory.id=64
    UNION ALL
    SELECT api_partcategory.id,api_partcategory.name,api_partcategory.parent_id, under_partcategory.level+1
    FROM api_partcategory JOIN under_partcategory ON api_partcategory.parent_id=under_partcategory.id
    ORDER BY 2
  )
SELECT * FROM under_partcategory;
''')

您也可以查看 https://github.com/django-mptt/django-mptt

这篇关于Django的递归查询集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！