38

Django中使用 Closure Table 储存无限分级数据

 5 years ago
source link: https://www.tuicool.com/articles/IviAVnF
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

起步

上一篇讨论了如何用数据库存储无限分级的数据。对于数据量大的情况（比如用户之间有邀请链，有点三级分销的意思），就要用到 closure table 的结构来进行存储。那么在 Django 中如何处理这个结构的模型呢？

定义模型

至少是要两个模型的，一个是存储分类，一个储存分类之间的关系：

class Category(models.Model):
    name = models.CharField(max_length=31)
    def __str__(self):
        return self.name

class CategoryRelation(models.Model):
    ancestor = models.ForeignKey(Category, null=True, related_name='ancestors', on_delete=models.SET_NULL, db_constraint=False, verbose_name='祖先')
    descendant = models.ForeignKey(Category,null=True,  related_name='descendants', on_delete=models.SET_NULL,
                                   db_constraint=False, verbose_name='子孙')
    distance = models.IntegerField()

    class Meta:
        unique_together = ("ancestor", "descendant")

数据操作

获得所有后代节点

class Category(models.Model):
    ...
    def get_descendants(self, include_self=False):
        """获得所有后代节点"""
        kw = {
            'descendants__ancestor' : self
        }
        if not include_self:
            kw['descendants__distance__gt'] = 0
        qs = Category.objects.filter(**kw).order_by('descendants__distance')
        return qs

获得直属下级

class Category(models.Model):
    ...
    def get_children(self):
        """获得直属下级"""
        qs = Category.objects.filter(descendants__ancestor=self, descendants__distance=1)
        return qs

节点的移动

节点的移动是比较难的，在 [ https://www.percona.com/blog/2011/02/14/moving-subtrees-in-closure-table/][1 ] 中讲述了，利用django能够执行原生的sql语句进行： 

def add_child(self, child):
        """将某个分类加入本分类，"""
        if CategoryRelation.objects.filter(ancestor=child, descendant=self).exists() \
                or CategoryRelation.objects.filter(ancestor=self, descendant=child, distance=1).exists():
            """child不能是self的祖先节点 or 它们已经是父子节点"""
            return

        # 如果表中不存在节点自身数据
        if not CategoryRelation.objects.filter(ancestor=child, descendant=child).exists():
            CategoryRelation.objects.create(ancestor=child, descendant=child, distance=0)
        table_name = CategoryRelation._meta.db_table
        cursor = connection.cursor()

        cursor.execute(f"""
            DELETE a
            FROM
                {table_name} AS a
            JOIN {table_name} AS d ON a.descendant_id = d.descendant_id
            LEFT JOIN {table_name} AS x ON x.ancestor_id = d.ancestor_id
            AND x.descendant_id = a.ancestor_id
            WHERE
                d.ancestor_id = {child.id}
            AND x.ancestor_id IS NULL;
        """)

        cursor.execute(f"""
        INSERT INTO {table_name} (ancestor_id, descendant_id, distance)
        SELECT supertree.ancestor_id, subtree.descendant_id,
        supertree.distance+subtree.distance+1
        FROM {table_name} AS supertree JOIN {table_name} AS subtree
        WHERE subtree.ancestor_id = {child.id}
        AND supertree.descendant_id = {self.id};
        """)

节点删除

节点删除有两种操作，一个是将所有子节点也删除，另一个是将自己点移到上级节点中。

扩展阅读

  • [ https://www.percona.com/blog/2011/02/14/moving-subtrees-in-closure-table/][2 ]

  • [ http://technobytz.com/closure_table_store_hierarchical_data.html][3 ]

    完整代码

    class Category(models.Model):
name = models.CharField(max_length=31)
def __str__(self):
    return self.name

def get_descendants(self, include_self=False):
    """获得所有后代节点"""
    kw = {
        'descendants__ancestor' : self
    }
    if not include_self:
        kw['descendants__distance__gt'] = 0
    qs = Category.objects.filter(**kw).order_by('descendants__distance')

    return qs

def get_children(self):
    """获得直属下级"""
    qs = Category.objects.filter(descendants__ancestor=self, descendants__distance=1)
    return qs

def get_ancestors(self, include_self=False):
    """获得所有祖先节点"""
    kw = {
        'ancestors__descendant': self
    }
    if not include_self:
        kw['ancestors__distance__gt'] = 0
    qs = Category.objects.filter(**kw).order_by('ancestors__distance')
    return qs

def get_parent(self):
    """分类仅有一个父节点"""
    parent = Category.objects.get(ancestors__descendant=self, ancestors__distance=1)
    return parent

def get_parents(self):
    """分类仅有一个父节点"""
    qs = Category.objects.filter(ancestors__descendant=self, ancestors__distance=1)
    return qs

def remove(self, delete_subtree=False):
    """删除节点"""
    if delete_subtree:
        # 删除所有子节点
        children_queryset = self.get_descendants(include_self=True)
        for child in children_queryset:
            CategoryRelation.objects.filter(Q(ancestor=child) | Q(descendant=child)).delete()
            child.delete()
    else:
        # 所有子节点移到上级
        parent = self.get_parent()
        children = self.get_children()
        for child in children:
            parent.add_chile(child)

        # CategoryRelation.objects.filter(descendant=self, distance=0).delete()
        CategoryRelation.objects.filter(Q(ancestor=self) | Q(descendant=self)).delete()
        self.delete()

def add_child(self, child):
    """将某个分类加入本分类，"""
    if CategoryRelation.objects.filter(ancestor=child, descendant=self).exists() \
            or CategoryRelation.objects.filter(ancestor=self, descendant=child, distance=1).exists():
        """child不能是self的祖先节点 or 它们已经是父子节点"""
        return

    # 如果表中不存在节点自身数据
    if not CategoryRelation.objects.filter(ancestor=child, descendant=child).exists():
        CategoryRelation.objects.create(ancestor=child, descendant=child, distance=0)
    table_name = CategoryRelation._meta.db_table
    cursor = connection.cursor()

    cursor.execute(f"""
        DELETE a
        FROM
            {table_name} AS a
        JOIN {table_name} AS d ON a.descendant_id = d.descendant_id
        LEFT JOIN {table_name} AS x ON x.ancestor_id = d.ancestor_id
        AND x.descendant_id = a.ancestor_id
        WHERE
            d.ancestor_id = {child.id}
        AND x.ancestor_id IS NULL;
    """)

    cursor.execute(f"""
    INSERT INTO {table_name} (ancestor_id, descendant_id, distance)
    SELECT supertree.ancestor_id, subtree.descendant_id,
    supertree.distance+subtree.distance+1
    FROM {table_name} AS supertree JOIN {table_name} AS subtree
    WHERE subtree.ancestor_id = {child.id}
    AND supertree.descendant_id = {self.id};
    """)

class CategoryRelation(models.Model): ancestor = models.ForeignKey(Category, null=True, related_name='ancestors', on_delete=models.SET_NULL, db_constraint=False, verbose_name='祖先') descendant = models.ForeignKey(Category,null=True, related_name='descendants', on_delete=models.SET_NULL, db_constraint=False, verbose_name='子孙') distance = models.IntegerField()

class Meta:
    unique_together = ("ancestor", "descendant")
[1]: https://www.percona.com/blog/2011/02/14/moving-subtrees-in-closure-table/
  [2]: https://www.percona.com/blog/2011/02/14/moving-subtrees-in-closure-table/
  [3]: http://technobytz.com/closure_table_store_hierarchical_data.html

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK