Django中使用 Closure Table 储存无限分级数据

时间:2022-11-19 10:03:39

这篇文章给大家介绍django中使用 closure table 储存无限分级数据,具体内容如下所述:

起步

对于数据量大的情况(比如用户之间有邀请链,有点三级分销的意思),就要用到 closure table 的结构来进行存储。那么在 django 中如何处理这个结构的模型呢?

定义模型

至少是要两个模型的,一个是存储分类,一个储存分类之间的关系:

?
1
2
3
4
5
6
7
8
9
10
11
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")

数据操作

获得所有后代节点

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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语句进行:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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 ]

完整代码

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
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 储存无限分级数据,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持!

原文链接:https://www.hongweipeng.com/index.php/archives/1802/