若依Django框架soft-delete导致的数据查询异常

 

在model中定义一个设备分组:

class DeviceGroup(CoreModel):
    name = models.CharField(blank=False, null=False, max_length=64, help_text='名称')
    dept = models.ForeignKey(Dept, blank=True, null=True, on_delete=models.CASCADE, help_text='部门')
    parent = models.ForeignKey('self', null=True, blank=True, related_name='children', verbose_name='上级分组',
                               on_delete=models.SET_NULL)
    is_root = models.BooleanField(default=False, help_text='是否根目录')
    index = models.IntegerField(default=0, help_text='排序索引')
    function_field = models.JSONField(blank=True, null=True, help_text='默认代码')
    function_text = models.TextField(blank=True, null=True, help_text='默认代码文本')
    is_show = models.BooleanField(default=True, help_text='是否显示')

其中parent定义了自己的上级分组,按照正常的查询逻辑,如果要返回树形结构只需要查询父节点为空的数据遍历下属分组即可,但是在实际的查询中会发现有一些节点显示不出来:

{
    "code": 2000,
    "data": {
        "page": 1,
        "limit": 1,
        "total": 1,
        "data": [
            {
                "id": 25,
                "modifier_name": "超级管理员",
                "creator_name": "超级管理员",
                "create_datetime": "2023-03-22 10:50:45",
                "update_datetime": "2023-03-22 10:50:45",
                "children": [
                    {
                        "id": 26,
                        "children": [],
                        "description": null,
                        "modifier": "2",
                        "dept_belong_id": "1",
                        "update_datetime": "2023-03-22 10:50:57",
                        "create_datetime": "2023-03-22 10:50:57",
                        "is_deleted": false,
                        "name": "123",
                        "is_root": false,
                        "index": 0,
                        "function_field": null,
                        "function_text": null,
                        "is_show": true
                    }
                ],
                "has_children": 1,
                "description": null,
                "modifier": "2",
                "dept_belong_id": "1",
                "is_deleted": false,
                "name": "1223",
                "is_root": false,
                "index": 0,
                "function_field": null,
                "function_text": null,
                "is_show": true
            }
        ]
    },
    "msg": "获取成功"
}

通过后台会发现数据并不是只有这么多:

当时比较好奇的一点是为什么父节点还在但是下面的数据却是空的,由于测试数据名字比较随意,导致想了很久才明白。虽然显示的都是1223但是这个设备分组却有不同的id,并不是同一个id。直接查看数据库会出现很多软删除的数据:

这些数据直接删除是删除不掉的,因为foreignkey约束导致删除失败:

对于这种数据需要在删除的时候断开子分类的关联,或者直接将子分类一并删除,保留子分类可以通过下面的代码删除这些软删除的数据:

def clear_delete_data():
    dgs = DeviceGroup.objects.filter(is_deleted=True)
    for d in dgs:
        DeviceGroup.objects.filter(parent =d).update(parent =None)
        d.delete()

删除之后重新拉取数据:

{
    "code": 2000,
    "data": {
        "page": 1,
        "limit": 1,
        "total": 3,
        "data": [
            {
                "id": 16,
                "modifier_name": "超级管理员",
                "creator_name": "超级管理员",
                "create_datetime": "2023-03-15 13:35:03",
                "update_datetime": "2023-03-15 14:22:22",
                "children": [
                    {
                        "id": 22,
                        "children": [
                            {
                                "id": 27,
                                "children": [],
                                "description": null,
                                "modifier": "2",
                                "dept_belong_id": "1",
                                "update_datetime": "2023-03-22 14:03:35",
                                "create_datetime": "2023-03-22 14:03:35",
                                "is_deleted": false,
                                "name": "BBB",
                                "is_root": false,
                                "index": 0,
                                "function_field": null,
                                "function_text": null,
                                "is_show": true
                            }
                        ],
                        "description": null,
                        "modifier": "2",
                        "dept_belong_id": "1",
                        "update_datetime": "2023-03-17 23:35:21",
                        "create_datetime": "2023-03-17 23:35:21",
                        "is_deleted": false,
                        "name": "测试设备A",
                        "is_root": false,
                        "index": 0,
                        "function_field": null,
                        "function_text": null,
                        "is_show": true
                    }
                ],
                "has_children": 1,
                "description": null,
                "modifier": "2",
                "dept_belong_id": "1",
                "is_deleted": false,
                "name": "201",
                "is_root": false,
                "index": 0,
                "function_field": null,
                "function_text": null,
                "is_show": true
            },
            {
                "id": 17,
                "modifier_name": "超级管理员",
                "creator_name": "超级管理员",
                "create_datetime": "2023-03-15 13:38:13",
                "update_datetime": "2023-03-15 13:38:13",
                "children": [],
                "has_children": 0,
                "description": null,
                "modifier": "2",
                "dept_belong_id": "1",
                "is_deleted": false,
                "name": "12345",
                "is_root": false,
                "index": 0,
                "function_field": null,
                "function_text": null,
                "is_show": true
            },
            {
                "id": 25,
                "modifier_name": "超级管理员",
                "creator_name": "超级管理员",
                "create_datetime": "2023-03-22 10:50:45",
                "update_datetime": "2023-03-22 10:50:45",
                "children": [
                    {
                        "id": 26,
                        "children": [],
                        "description": null,
                        "modifier": "2",
                        "dept_belong_id": "1",
                        "update_datetime": "2023-03-22 10:50:57",
                        "create_datetime": "2023-03-22 10:50:57",
                        "is_deleted": false,
                        "name": "123",
                        "is_root": false,
                        "index": 0,
                        "function_field": null,
                        "function_text": null,
                        "is_show": true
                    }
                ],
                "has_children": 1,
                "description": null,
                "modifier": "2",
                "dept_belong_id": "1",
                "is_deleted": false,
                "name": "1223",
                "is_root": false,
                "index": 0,
                "function_field": null,
                "function_text": null,
                "is_show": true
            }
        ]
    },
    "msg": "获取成功"
}

要彻底解决这个问题,可以重写view的destroy方法:

def destroy(self, request, *args, **kwargs):
    '''重写删除代码 删除之后父分类设置为空'''
    instance = self.get_object()
    DeviceGroup.objects.filter(parent=instance).update(parent=None)
    instance.delete()
    return DetailResponse(data=[], msg="删除成功")
☆版权☆

* 网站名称:obaby@mars
* 网址:https://lang.ma/
* 个性:https://oba.by/
* 本文标题: 《若依Django框架soft-delete导致的数据查询异常》
* 本文链接:https://lang.ma/2023/03/11588
* 短链接:https://oba.by/?p=11588
* 转载文章请标明文章来源,原文标题以及原文链接。请遵从 《署名-非商业性使用-相同方式共享 2.5 中国大陆 (CC BY-NC-SA 2.5 CN) 》许可协议。


You may also like

3 comments

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注