Django中使用 Closure Table 储存无限分级数据
程序员文章站
2023-11-21 09:43:28
这篇文章给大家介绍django中使用 closure table 储存无限分级数据,具体内容如下所述:
起步
对于数据量大的情况(比如用户之间有邀请链,有点三级分销的意...
这篇文章给大家介绍django中使用 closure table 储存无限分级数据,具体内容如下所述:
起步
对于数据量大的情况(比如用户之间有邀请链,有点三级分销的意思),就要用到 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
节点的移动
节点的移动是比较难的,在 [ ] 中讲述了,利用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}; """)
节点删除
节点删除有两种操作,一个是将所有子节点也删除,另一个是将自己点移到上级节点中。
扩展阅读
[ ]
[ ]
完整代码
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
总结
以上所述是小编给大家介绍的django中使用 closure table 储存无限分级数据,希望对大家有所帮助