圆月山庄资源网

Django中使用 Closure Table 储存无限分级数据
脚本专栏 2024/11/6 佚名
3 2
圆月山庄资源网 Design By www.vgjia.com

这篇文章给大家介绍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,django分级数据
圆月山庄资源网 Design By www.vgjia.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
圆月山庄资源网 Design By www.vgjia.com
www.vgjia.com 圆月山庄资源网
139,976互联网资源
144,792高清电影
21,817无损音乐
631,128技术资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接