相思资源网 Design By www.200059.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 储存无限分级数据，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！
相思资源网 Design By www.200059.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
相思资源网 Design By www.200059.com
暂无Django中使用 Closure Table 储存无限分级数据的评论...
《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线
暴雪近日发布了《魔兽世界》10.2.6 更新内容，新游玩模式《强袭风暴》即将于3月21 日在亚服上线，届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。
艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕，并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时，他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中，玩家将会进入一个全新的海盗主题大逃杀式限时活动，其中包含极高的风险和史诗级的奖励。
《强袭风暴》不是普通的战场，作为一个独立于主游戏之外的活动，玩家可以用大逃杀的风格来体验《魔兽世界》，不分职业、不分装备（除了你在赛局中捡到的），光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式，玩家在加入海盗主题的预赛大厅区域前，可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹，《巨龙崛起》和《魔兽世界：巫妖王之怒 经典版》的玩家都可以获得奖励。