Django中使用 Closure Table 储存无限分级数据
作者:栖迟于一丘 时间:2021-05-25 03:05:07
这篇文章给大家介绍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 储存无限分级数据网站的支持!
来源:https://www.hongweipeng.com/index.php/archives/1802/
标签:django,Closure,Table,分级数据
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Appium+Python实现简单的自动化登录测试的实现
2021-09-13 05:49:14
![](https://img.aspxhome.com/file/2023/9/82709_0s.png)
使用numpy实现矩阵的翻转(flip)与旋转
2023-01-31 01:03:18
使用Pyinstaller转换.py文件为.exe可执行程序过程详解
2022-11-30 22:43:40
![](https://img.aspxhome.com/file/2023/8/79728_0s.png)
python 计算一个字符串中所有数字的和实例
2022-07-19 01:42:33
python 将numpy维度不同的数组相加相乘操作
2022-03-26 06:14:06
![](https://img.aspxhome.com/file/2023/5/93425_0s.jpg)
python Windows最新版本安装教程
2023-02-25 21:09:52
![](https://img.aspxhome.com/file/2023/4/93434_0s.png)
python3 numpy中数组相乘np.dot(a,b)运算的规则说明
2022-05-08 16:23:29
![](https://img.aspxhome.com/file/2023/5/99145_0s.jpg)
Django-silk性能测试工具安装及使用解析
2023-12-08 08:53:19
用javascript获得地址参数的两种方法
2008-04-30 12:17:00
go Cobra命令行工具入门教程
2023-06-24 18:27:12
![](https://img.aspxhome.com/file/2023/6/81566_0s.jpg)
VMware中linux环境下oracle安装图文教程(二)ORACLE 10.2.05版本的升级补丁安装
2023-07-22 11:37:11
![](https://img.aspxhome.com/file/2023/1/64091_0s.jpg)
Pandas如何对Categorical类型字段数据统计实战案例
2023-03-01 20:02:26
ACCESS的参数化查询 附ASP和C#(ASP.NET)函数
2008-01-10 12:18:00
Python Pygame实现落球游戏详解
2021-06-23 00:54:38
![](https://img.aspxhome.com/file/2023/0/94140_0s.png)
Oracle 数据库中创建合理的数据库索引
2009-07-02 12:31:00
python可视化 matplotlib画图使用colorbar工具自定义颜色
2023-01-06 07:10:01
![](https://img.aspxhome.com/file/2023/2/94022_0s.png)
Python pyecharts案例超市4年数据可视化分析
2021-04-09 21:10:29
![](https://img.aspxhome.com/file/2023/3/77643_0s.gif)
asp智能脏话过滤系统v1.0
2011-04-14 11:00:00
如何判断JavaScript变量的类型
2009-02-25 12:28:00
教你使用Python根据模板批量生成docx文档
2021-12-27 00:35:13
![](https://img.aspxhome.com/file/2023/2/76082_0s.png)