高性能的无限极分类的增删改查

无限极更新递归法

    //分类更新处理
    public function categoryUpdate(Request $request, Category $category)
    {
   
        //表单验证
        $validator = Validator::make($request->all(), [
            'pid' => ['required', 'integer', 'min:0'],
            'name' => [
                'required',
                'min:2',
                'max:8',
                Rule::unique('categories')->where(function ($query) use ($request, $category) {
   
                    $query->where('user_id', auth()->id())->where('name', $request->get('name'))->where('id', "!=", $category->id);
                })
            ]
        ]);


        if ($validator->fails()) {
   //表单验证不通过
            return redirect(route('console.categoryEdit',$category))->withErrors($validator->errors()->first());
        }

        $pid = $request->get('pid');
        $name = $request->get('name');
        $user_id = auth()->id();


        //组合数据
        $data = [
            'pid'=>$pid,
            'name'=>$name,
            'user_id'=>$user_id,
        ];
        //如果是顶级就默认为0
        if($data['pid'] == 0){
   
            $data['level'] = 0;
            $data['pid_path'] = 0;
            $data['pid_path_name'] = '';

        }else{
    //查询主键id查询出该分类的父级分类数据，这里的主键id就是当前传递分类的pid

            $p_cate = Category::find($data['pid'])->toArray();

            if(empty($p_cate)){
   

                return redirect(route('console.categoryEdit',$category))->withErrors('数据异常，请刷新重试');

            }
            $data['level'] = $p_cate['level'] + 1;
            $data['pid_path'] = $p_cate['pid_path'] . '_' . $p_cate['id'];
            $data['pid_path_name'] = trim($p_cate['pid_path_name'] . '/' . $p_cate['name'], '/');
        }

        //得到传递过来的分类的id
        $id = $category->id;
        $category->update($data);


        //递归更新所有的下级
        $this->up_cate($id);
        return redirect(route('console.categoryEdit',$category))->with('success', '分类修改成功');

    }

    //递归更新
    public function up_cate($id) {
   
        //查询出该数据
        $u_cate = Category::find($id);
        //要更新的数据
        $dt = [];
        $dt['level'] = $u_cate['level'] + 1;
        $dt['pid_path'] = $u_cate['pid_path'] . '_' . $u_cate['id'];
        $dt['pid_path_name'] = trim($u_cate['pid_path_name'] . '/' . $u_cate['name'], '/');

        //查询是否还有下级
        $count = Category::where('pid',$id)->first();
        if($count !== null ){
   
            $count->update($dt);
            $this->up_cate($count->id);
        }
    }

无限极更新字符串替换法

    //分类更新处理
    public function categoryUpdate(Request $request, Category $category)
    {
   
        //表单验证
        $validator = Validator::make($request->all(), [
            'pid' => ['required', 'integer', 'min:0'],
            'name' => [
                'required',
                'min:2',
                'max:8',
                Rule::unique('categories')->where(function ($query) use ($request, $category) {
   
                    $query->where('user_id', auth()->id())->where('name', $request->get('name'))->where('id', "!=", $category->id);
                })
            ]
        ]);


        if ($validator->fails()) {
   //表单验证不通过
            return redirect(route('console.categoryEdit', $category))->withErrors($validator->errors()->first());
        }

        $pid = $request->get('pid');
        $name = $request->get('name');
        $user_id = auth()->id();


        //组合数据
        $data = [
            'pid' => $pid,
            'name' => $name,
            'user_id' => $user_id,
        ];
        //如果是顶级就默认为0
        if ($data['pid'] == 0) {
   
            $data['level'] = 0;
            $data['pid_path'] = 0;
            $data['pid_path_name'] = '';

        } else {
    //查询主键id查询出该分类的父级分类数据，这里的主键id就是当前传递分类的pid

            $p_cate = Category::find($data['pid'])->toArray();

            if (empty($p_cate)) {
   

                return redirect(route('console.categoryEdit', $category))->withErrors('数据异常，请刷新重试');

            }
            $data['level'] = $p_cate['level'] + 1;
            $data['pid_path'] = $p_cate['pid_path'] . '_' . $p_cate['id'];
            $data['pid_path_name'] = trim($p_cate['pid_path_name'] . '/' . $p_cate['name'], '/');
        }


        //得到当前分类id
        $id = $category->id;

        //得到当前分类的旧的pid_path
        $curr_old_pid_path = $category->pid_path;
        //得到当前分类的旧的pid_path_name
        $curr_old_pid_path_name = $category->pid_path_name;


        //查询出包括自己的数组
        $ffs = Category::where('pid_path', 'like', "%{
   $curr_old_pid_path}%")->where('id', '!=', $id)->get(['id', 'pid_path', 'pid_path_name'])->keyBy('id')->toArray();


        //更新操作,tap得到更新后的数据
        $update_cate = tap($category)->update($data);

        //维护pid_path 和 pid_path_name  字段
        //遍历数组进行字符串替换
        foreach ($ffs as &$ff) {
   

            $ff['pid_path'] = str_replace($curr_old_pid_path, $update_cate['pid_path'], $ff['pid_path']);

            if($update_cate['pid'] == 0){
   
                $ff['pid_path_name'] = str_replace($curr_old_pid_path_name."/" , $update_cate['pid_path_name'] , $ff['pid_path_name']);
            }else{
   
                $ff['pid_path_name'] = str_replace($curr_old_pid_path_name , $update_cate['pid_path_name'] , $ff['pid_path_name']);
            }


            //    后端/php/tp/3.2   把这一串     后端/php/tp  去掉  取剩余 的 /3.2


        }


        (new Category())->updateBatch($ffs);
        return redirect(route('console.categoryEdit', $category))->with('success', '分类修改成功');

    }

laravel中批量更新的方法

    //批量更新
    public function updateBatch($multipleData = [])
    {
   
        try {
   
            if (empty($multipleData)) {
   
                throw new \Exception("数据不能为空");
            }

            //new self()
            $tableName = DB::getTablePrefix() . $this->getTable(); // 表名
            $firstRow = current($multipleData);
            $updateColumn = array_keys($firstRow);
            // 默认以id为条件更新，如果没有ID则以第一个字段为条件
            $referenceColumn = isset($firstRow['id']) ? 'id' : current($updateColumn);
            unset($updateColumn[0]);
            // 拼接sql语句
            $updateSql = "UPDATE " . $tableName . " SET ";
            $sets = [];
            $bindings = [];
            foreach ($updateColumn as $uColumn) {
   
                $setSql = "`" . $uColumn . "` = CASE ";
                foreach ($multipleData as $data) {
   
                    $setSql .= "WHEN `" . $referenceColumn . "` = ? THEN ? ";
                    $bindings[] = $data[$referenceColumn];
                    $bindings[] = $data[$uColumn];
                }
                $setSql .= "ELSE `" . $uColumn . "` END ";
                $sets[] = $setSql;
            }
            $updateSql .= implode(', ', $sets);
            $whereIn = collect($multipleData)->pluck($referenceColumn)->values()->all();
            $bindings = array_merge($bindings, $whereIn);
            $whereIn = rtrim(str_repeat('?,', count($whereIn)), ',');
            $updateSql = rtrim($updateSql, ", ") . " WHERE `" . $referenceColumn . "` IN (" . $whereIn . ")";
            // 传入预处理sql语句和对应绑定数据
            return DB::update($updateSql, $bindings);
        } catch (\Exception $e) {
   
            return false;
        }
    }

前端展示

  <div class="table-responsive">
                                <table class="table table-hover">
                                    <thead>