Django中使用 Closure Table 储存无限分级数据
这篇文章给大家介绍Django中使用ClosureTable储存无限分级数据，具体内容如下所述：
起步
对于数据量大的情况（比如用户之间有邀请链，有点三级分销的意思），就要用到closuretable的结构来进行存储。那么在Django中如何处理这个结构的模型呢？
定义模型
至少是要两个模型的，一个是存储分类，一个储存分类之间的关系：
classCategory(models.Model):
name=models.CharField(max_length=31)
def__str__(self):
returnself.name
classCategoryRelation(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()
classMeta:
unique_together=("ancestor","descendant")
数据操作
获得所有后代节点
classCategory(models.Model):
...
defget_descendants(self,include_self=False):
"""获得所有后代节点"""
kw={
'descendants__ancestor':self
}
ifnotinclude_self:
kw['descendants__distance__gt']=0
qs=Category.objects.filter(**kw).order_by('descendants__distance')
returnqs获得直属下级
classCategory(models.Model):
...
defget_children(self):
"""获得直属下级"""
qs=Category.objects.filter(descendants__ancestor=self,descendants__distance=1)
returnqs
节点的移动
节点的移动是比较难的，在[https://www.percona.com/blog/2011/02/14/moving-subtrees-in-closure-table/][1]中讲述了，利用django能够执行原生的sql语句进行：
defadd_child(self,child):
"""将某个分类加入本分类，"""
ifCategoryRelation.objects.filter(ancestor=child,descendant=self).exists()\
orCategoryRelation.objects.filter(ancestor=self,descendant=child,distance=1).exists():
"""child不能是self的祖先节点or它们已经是父子节点"""
return
#如果表中不存在节点自身数据
ifnotCategoryRelation.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"""
DELETEa
FROM
{table_name}ASa
JOIN{table_name}ASdONa.descendant_id=d.descendant_id
LEFTJOIN{table_name}ASxONx.ancestor_id=d.ancestor_id
ANDx.descendant_id=a.ancestor_id
WHERE
d.ancestor_id={child.id}
ANDx.ancestor_idISNULL;
""")
cursor.execute(f"""
INSERTINTO{table_name}(ancestor_id,descendant_id,distance)
SELECTsupertree.ancestor_id,subtree.descendant_id,
supertree.distance+subtree.distance+1
FROM{table_name}ASsupertreeJOIN{table_name}ASsubtree
WHEREsubtree.ancestor_id={child.id}
ANDsupertree.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]
完整代码
classCategory(models.Model):
name=models.CharField(max_length=31)
def__str__(self):
returnself.name
defget_descendants(self,include_self=False):
"""获得所有后代节点"""
kw={
'descendants__ancestor':self
}
ifnotinclude_self:
kw['descendants__distance__gt']=0
qs=Category.objects.filter(**kw).order_by('descendants__distance')
returnqs
defget_children(self):
"""获得直属下级"""
qs=Category.objects.filter(descendants__ancestor=self,descendants__distance=1)
returnqs
defget_ancestors(self,include_self=False):
"""获得所有祖先节点"""
kw={
'ancestors__descendant':self
}
ifnotinclude_self:
kw['ancestors__distance__gt']=0
qs=Category.objects.filter(**kw).order_by('ancestors__distance')
returnqs
defget_parent(self):
"""分类仅有一个父节点"""
parent=Category.objects.get(ancestors__descendant=self,ancestors__distance=1)
returnparent
defget_parents(self):
"""分类仅有一个父节点"""
qs=Category.objects.filter(ancestors__descendant=self,ancestors__distance=1)
returnqs
defremove(self,delete_subtree=False):
"""删除节点"""
ifdelete_subtree:
#删除所有子节点
children_queryset=self.get_descendants(include_self=True)
forchildinchildren_queryset:
CategoryRelation.objects.filter(Q(ancestor=child)|Q(descendant=child)).delete()
child.delete()
else:
#所有子节点移到上级
parent=self.get_parent()
children=self.get_children()
forchildinchildren:
parent.add_chile(child)
#CategoryRelation.objects.filter(descendant=self,distance=0).delete()
CategoryRelation.objects.filter(Q(ancestor=self)|Q(descendant=self)).delete()
self.delete()
defadd_child(self,child):
"""将某个分类加入本分类，"""
ifCategoryRelation.objects.filter(ancestor=child,descendant=self).exists()\
orCategoryRelation.objects.filter(ancestor=self,descendant=child,distance=1).exists():
"""child不能是self的祖先节点or它们已经是父子节点"""
return
#如果表中不存在节点自身数据
ifnotCategoryRelation.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"""
DELETEa
FROM
{table_name}ASa
JOIN{table_name}ASdONa.descendant_id=d.descendant_id
LEFTJOIN{table_name}ASxONx.ancestor_id=d.ancestor_id
ANDx.descendant_id=a.ancestor_id
WHERE
d.ancestor_id={child.id}
ANDx.ancestor_idISNULL;
""")
cursor.execute(f"""
INSERTINTO{table_name}(ancestor_id,descendant_id,distance)
SELECTsupertree.ancestor_id,subtree.descendant_id,
supertree.distance+subtree.distance+1
FROM{table_name}ASsupertreeJOIN{table_name}ASsubtree
WHEREsubtree.ancestor_id={child.id}
ANDsupertree.descendant_id={self.id};
""")classCategoryRelation(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()
classMeta:
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中使用ClosureTable储存无限分级数据，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！