Design python+mysql database with multi-level menu directory

1. Database design

for multilevel directory design, the simplest and most direct way, can directly put the directory name and content and other related information into a table, how to distinguish the top-level directory and subdirectory is the key to the design.

thought :

you can think of a directory tree as a tree structure with multiple root nodes, subdirectories being sons, parent directories being parents, and each subdirectory having only one parent node. Therefore, at the time of design, the parent_ID of the top-level directory can be set as NULL, and all top-level directories can be directly searched out according to NULL. How to add child nodes?Each subdirectory has a parent_ID, and the value of parent_ID corresponds to the ID of other records, so that a tree can be formed by parent_ID and ID. Get to the root node, then iterate through each of the root node leaf nodes, if there is a leaf node, then hung beneath it, such as id for the object of the 5 parent_id is 1, then put the data on id not the record of 1, id is 20 records the parent_id is 5, then hang the id for the record of 20 id for 5 record, json structure is as follows: 

           
         {
            "id": 1,
            "text": "顶级目录",
            "parent_menu_tree_item": null,
            "menu_type": 0,
            "data": [
                {
                    "id": 5,
                    "text": "二级目录",
                    "parent_menu_tree_item": 1,
                    "menu_type": 0,
                    "data": {
                        "id": 20,
                        "text": "三级目录",
                        "parent_menu_tree_item": 5,
                        "menu_type": 0
                    }
                }
             ]
         }

ii. Programming

idea: recursion. We select all null records with parent_ID, that is, the root node, and then traverse through each parent subtree. If there are any, we recursively traverse through all the subtrees, each time hanging on the I [“data”] of the parent node, and then traverse to the next root node.

core code is as follows :

1) gets all root nodes :

items = HandsOnCaseMenuTreeItem.objects.using("admin").filter(parent_menu_tree_item=None,
                                                                          resource_id=resource_id) \
                .values("id", "text", "parent_menu_tree_item", "menu_type")
            datas = []
            for i in items:
                find_child(i, datas, resource_id)
            r.data = datas

2) recursively traverses all root nodes :

def find_child(i, datas):
    p_id = i["id"]
    p = HandsOnCaseMenuTreeItem.objects \
        .filter(parent_menu_tree_item=p_id) \
        .values("id", "text", "parent_menu_tree_item", "menu_type")
    if len(p) > 1:
        i['data'] = list(p)
        datas.append(i)
        for j in p:
            find_child(j, datas)
    elif len(p) == 1:
        find_child(p[0], datas)
        i["data"] = p[0]
    return datas


Read More: