自关联分类获取子级分类⭐⭐
前情提要
有这么一个表 自关联获取分类
获取顶级分类 选择 返回顶级与二级分类
获取二级分类 选择 返回二级与三级分类
获取三级分类 选择 ...
创建数据表 添加测试数据⭐⭐
建议 使用sql文件
/*
Navicat Premium Data Transfer
Source Server : loacalhost
Source Server Type : MySQL
Source Server Version : 50732
Source Host : localhost:3306
Source Schema : ujedu
Target Server Type : MySQL
Target Server Version : 50732
File Encoding : 65001
Date: 23/03/2021 11:30:07
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for search_category
-- ----------------------------
DROP TABLE IF EXISTS `search_category`;
CREATE TABLE `search_category` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`category_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分类名字',
`user_search_foreign` int(11) NULL DEFAULT NULL,
`create_time` datetime(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
`update_time` datetime(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
`site` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'US',
`pid` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `user_search_id`(`user_search_foreign`) USING BTREE,
CONSTRAINT `user_search_id` FOREIGN KEY (`user_search_foreign`) REFERENCES `tb_users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of search_category
-- ----------------------------
INSERT INTO `search_category` VALUES (1, 'ceshi01', 1, '2021-03-16 09:19:55.000000', '2021-03-16 09:19:57.000000', 'US', NULL);
INSERT INTO `search_category` VALUES (2, 'ceshi02', 1, '2021-03-16 10:39:14.506797', '2021-03-16 10:39:14.506797', 'US', NULL);
INSERT INTO `search_category` VALUES (3, 'ceshi03', 1, '2021-03-17 11:42:02.000000', '2021-03-17 11:42:04.000000', 'US', NULL);
INSERT INTO `search_category` VALUES (4, 'upload01', 1, '2021-03-18 16:00:23.422573', '2021-03-18 16:00:23.422573', 'US', NULL);
INSERT INTO `search_category` VALUES (7, 'ceshi01', 1, '2021-03-19 10:42:19.054462', '2021-03-19 10:42:19.054462', 'UK', NULL);
INSERT INTO `search_category` VALUES (8, 'ceshi02', 1, '2021-03-19 10:43:15.092313', '2021-03-19 10:43:15.092313', 'UK', 7);
INSERT INTO `search_category` VALUES (9, 'ceshi03', 1, '2021-03-19 10:58:28.897592', '2021-03-19 10:58:28.897592', 'UK', 7);
INSERT INTO `search_category` VALUES (10, '三级分类1', 1, '2021-03-23 09:58:13.728414', '2021-03-23 09:58:13.728414', 'UK', 8);
INSERT INTO `search_category` VALUES (11, '三级分类2', 1, NULL, NULL, 'UK', 8);
INSERT INTO `search_category` VALUES (12, '三级分类3', 1, NULL, NULL, 'UK', 9);
INSERT INTO `search_category` VALUES (13, 'ceshi04', 1, NULL, NULL, 'UK', 7);
SET FOREIGN_KEY_CHECKS = 1;
根据条件 获取对应的分类⭐⭐
视图函数 pid 的作用: pid 代表是否存在顶级分类id 不存在则是顶级
class SearchCateGoryView(APIView):
authentication_classes = [MyBaseAuthentication, ]
def get(self, request):
user = is_master(request.user)
site = request.GET.get('site', 'UK')
pid = request.GET.get('pid')
if not pid or pid is None:
queryset = SearchCategory.objects.filter(user_search_foreign=user.id, site=site)
category_list = get_sub_category(queryset, 1, user.id, site)
return Response({'code': RET.OK, "cateList": category_list})
else:
queryset = SearchCategory.objects.filter(user_search_foreign=user.id, site=site, pid=pid)
category_list = get_sub_category(queryset, 2, user.id, site)
return Response({'code': RET.OK, "cateList": category_list})
获取分类数据的函数 get_sub_category() filter_sub: 确认获取顶级 还是 获取子级分类
def get_sub_category(queryset, filter_sub, uid, site):
category_list = []
for info in queryset:
if filter_sub == 1:
if not info.pid:
d = get_dict_info(info, queryset)
category_list.append(d)
else:
# 传递pid 获取这个分类下的子级分类 子级分类的pid与分类id相同的 则添加 否则[]
three_queryset = SearchCategory.objects.filter(user_search_foreign=uid, site=site, pid=info.id)
d = get_dict_info(info, three_queryset)
category_list.append(d)
return category_list
组合返回的格式 get_dict_info() if info.id == info_pid.pid: 如果存在子级 就添加到字典 否则为[]
def get_dict_info(info, queryset):
d = {'id': info.id, 'categoryName': info.category_name}
c = []
if queryset.count() <= 0:
d['children'] = c
for info_pid in queryset:
if info.id == info_pid.pid:
e = {'id': info_pid.id, 'categoryName': info_pid.category_name}
c.append(e)
d['children'] = c
return d
