Django中使用 Closure Table 储存无限分级数据
2019/7/14 23:07:07
本文主要是介绍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
节点的移动
节点的移动是比较难的,在 [ 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
总结
以上所述是小编给大家介绍的Django中使用 Closure Table 储存无限分级数据,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对找一找教程网网站的支持!
这篇关于Django中使用 Closure Table 储存无限分级数据的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-24Python编程基础详解
- 2024-11-21Python编程基础教程
- 2024-11-20Python编程基础与实践
- 2024-11-20Python编程基础与高级应用
- 2024-11-19Python 基础编程教程
- 2024-11-19Python基础入门教程
- 2024-11-17在FastAPI项目中添加一个生产级别的数据库——本地环境搭建指南
- 2024-11-16`PyMuPDF4LLM`:提取PDF数据的神器
- 2024-11-16四种数据科学Web界面框架快速对比:Rio、Reflex、Streamlit和Plotly Dash
- 2024-11-14获取参数学习:Python编程入门教程