1、新建中间表

需要查询的表字段通过中间表进行管理

CREATE TABLE `coltables` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `uuid` varchar(60) NOT NULL COMMENT 'uuid',  `order` int(11) NOT NULL DEFAULT '1' COMMENT '排序',  `tb` varchar(30) NOT NULL COMMENT '表名',  `tbcn` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '表中文名',  `col` varchar(30) NOT NULL COMMENT '字段名',  `colcn` varchar(30) NOT NULL COMMENT '字段中文名',  `classmark` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '配置项',  `type` varchar(10) DEFAULT 'cn' COMMENT '编码/中文',  `last_change` varchar(120) DEFAULT NULL COMMENT '修改标识',  `deleted_at` timestamp NULL DEFAULT NULL,  `created_at` timestamp NULL DEFAULT NULL,  `updated_at` timestamp NULL DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `coltables_uuid_unique` (`uuid`),  KEY `coltables_tb_col_index` (`tb`,`col`),  KEY `coltables_order_index` (`order`),  KEY `coltables_last_change_index` (`last_change`)) ENGINE=InnoDB AUTO_INCREMENT=134 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;复制代码

2、配置项通过Laravel Settings存储

• 安装：composer require anlutro/l4-settings
• 导出配置项： Publish the config file by running php artisan vendor:publish --provider="anlutro\LaravelSettings\ServiceProvider" --tag="config".

3、adv_search.blade.php代码

    
    
     
        
      
            
       
                
        
                    
         
 
           
          
 
           左括号 
          
 
           
           
          
 
           筛选条件 
          
 
           
           
          
 
           比较符 
          
 
           
           
          
 
           选项或输入值 
          
 
           
           
          
 
           右括号 
          
 
           
           
          
 
           关系符 
          
 
          
 
           清空 
          
 
           
         
                    
                             
         
 
           
          
 
            
          
 
           
           
          
 
             
            rememberForever($c_list_name, function () { return \App\Entities\Backend\Xtgl\Coltable::all(); }); $datas = $datas->filter(function ($data, $key) use ($adv_tables) { //过滤：只选择 表名在 配置项 中的字段集合 return in_array($data->tb, Setting::get($adv_tables)); })->sortBy(function ($data, $key) use ($adv_tables) { //使用 表名在 配置项 中的序号 + order 对集合进行重新排序 return array_search($data->tb, Setting::get($adv_tables)) .'_'.sprintf("%04d", $data->order); }); foreach ($datas as $data) { ?> { { $data->tbcn . '-' . $data->colcn }} 
              
          
 
           
           
          
 
            包含 不包含 等于 不等于 大于 大于等于 小于 小于等于  
          
 
           
           
          
 
            
          
 
           
           
          
 
            
          
 
           
           
          
 
            与 或  
          
 
          
 
            
          
 
           
         
                    
                         
        
                
        
                    
          关闭                    
          设置                    
          清空                    
          查询                
        
            
       
        
      
    
     
    
复制代码

4、adv_search_js.blade.php代码

复制代码

5、controller部分代码

/**     * 返回layui_table数据     *     * @param \Illuminate\Http\Request $request     *     * @return \Illuminate\Http\JsonResponse     */    public function person_list(Request $request)    {        //多条件查询开始：组装 各个表的查询字符串        $where_p    = "";        $where_c    = "";        $where_m    = "";        $where_ck   = "";        $where_w    = "";        $where_y    = "";        $where_h    = "";        if ($request->has(['left', 'field', 'choose'])) {            $left   = $request->post('left');//左括号            $field  = $request->post('field');//字段            $choose = $request->post('choose');//比较符            $value  = $request->post('value');//值            $right  = $request->post('right');//右括号            $rela   = $request->post('rela');//逻辑比较符            collect($field)->each(function ($item, $key) use (&$where_p, &$where_c, &$where_m, &$where_ck, &$where_h, &$where_w, &$where_y, $left, $choose, $value, $right, $rela) {                if ($item) {                    $tmp        = explode('.', $item);                    $value_t    = "'{$value[$key]}'";                    if (in_array($choose[$key], ['like', 'not like'])) {                        $value_t = "'%{$value[$key]}%'";                    }                    if ($tmp[0] == 'persons') {                        if ($tmp[1] == 'unitcode') {
   //所在单位要特殊处理                            if ($where_p)//已经有值的时候要添加上一层的`逻辑比较符`                                $where_p .= " " . $rela[$key-1];                            $where_p .= " FIND_IN_SET(unitcode, getChildList('{$value[$key]}', 1))";                        } else {                            if ($where_p)//已经有值的时候要添加上一层的`逻辑比较符`                                $where_p .= " " . $rela[$key-1];                            $where_p .= " " . $left[$key] . " " . $tmp[1] . " " . $choose[$key] . " " . $value_t . " " . $right[$key];                        }                    } elseif ($tmp[0] == 'children') {                        if ($where_c)//已经有值的时候要添加上一层的`逻辑比较符`                            $where_c .= " " . $rela[$key-1];                        $where_c .= " " . $left[$key] . " " . $tmp[1] . " " . $choose[$key] . " " . $value_t . " " . $right[$key];                    } elseif ($tmp[0] == 'checks') {                        if ($where_ck)//已经有值的时候要添加上一层的`逻辑比较符`                            $where_ck .= " " . $rela[$key-1];                        $where_ck .= " " . $left[$key] . " " . $tmp[1] . " " . $choose[$key] . " " . $value_t . " " . $right[$key];                    } elseif ($tmp[0] == 'marrows') {                        if ($where_m)//已经有值的时候要添加上一层的`逻辑比较符`                            $where_m .= " " . $rela[$key-1];                        $where_m .= " " . $left[$key] . " " . $tmp[1] . " " . $choose[$key] . " " . $value_t . " " . $right[$key];                    } elseif ($tmp[0] == 'holidays') {                        if ($where_h)//已经有值的时候要添加上一层的`逻辑比较符`                            $where_h .= " " . $rela[$key-1];                        $where_h .= " " . $left[$key] . " " . $tmp[1] . " " . $choose[$key] . " " . $value_t . " " . $right[$key];                    } elseif ($tmp[0] == 'jsways') {                        if ($where_w)//已经有值的时候要添加上一层的`逻辑比较符`                            $where_w .= " " . $rela[$key-1];                        $where_w .= " " . $left[$key] . " " . $tmp[1] . " " . $choose[$key] . " " . $value_t . " " . $right[$key];                    } elseif ($tmp[0] == 'jsycs') {                        if ($where_y)//已经有值的时候要添加上一层的`逻辑比较符`                            $where_y .= " " . $rela[$key-1];                        $where_y .= " " . $left[$key] . " " . $tmp[1] . " " . $choose[$key] . " " . $value_t . " " . $right[$key];                    }                }            });        }        //多条件查询结束：组装 各个表的查询字符串完毕        $page           = $request->get('page', 1);        $limit          = $request->get('limit', 10);        $unitcode       = $request->get('unitcode', '%');        $name           = $request->post('name');        $filter_type    = $request->post('filter_type', 'all');        $uuids          = $request->post('uuids');        $this->repository->setPresenter(PersonPresenter::class);        if (!$name)            $name       = $request->get('name');        $total          = $this->repository            ->orderBy('unitcode')            ->orderBy('name1')            ->scopeQuery(function ($query) use ($unitcode, $name, $filter_type, $uuids, $where_c, $where_p, $where_ck, $where_h, $where_m, $where_w, $where_y) {                //高级条件搜索                if ($where_p || $where_m || $where_c || $where_ck || $where_h || $where_w || $where_y) {                    if ($where_p)                        $query = $query->whereRaw($where_p);                    if ($where_m)                        $query = $query->whereHas('marrows', function($xquery) use ($where_m) {                            $xquery->withoutGlobalScopes(['user_power'])->whereRaw($where_m);                        });                    if ($where_c)                        $query = $query->whereHas('children', function($xquery) use ($where_c) {                            $xquery->withoutGlobalScopes(['user_power'])->whereRaw($where_c);                        });                    if ($where_ck)                        $query = $query->whereHas('checks', function($xquery) use ($where_ck) {                            $xquery->withoutGlobalScopes(['user_power'])->whereRaw($where_ck);                        });                    if ($where_h)                        $query = $query->whereHas('holidays', function($xquery) use ($where_h) {                            $xquery->withoutGlobalScopes(['user_power'])->whereRaw($where_h);                        });                    if ($where_w)                        $query = $query->whereHas('jsways', function($xquery) use ($where_w) {                            $xquery->withoutGlobalScopes(['user_power'])->whereRaw($where_w);                        });                    if ($where_y)                        $query = $query->whereHas('jsycs', function($xquery) use ($where_y) {                            $xquery->withoutGlobalScopes(['user_power'])->whereRaw($where_y);                        });                    return $query;                }                //批量删除或者恢复                if ($uuids)                    return $query->whereIn('uuid', $uuids);                //是否过滤已删除名单                if ($filter_type == 'deleted')                    return $name ? $query->onlyTrashed()->whereRaw('concat(name1, code1) like ?', ["%{$name}%"])->orderBy('deleted_at', 'desc')                        : $query->onlyTrashed()->orderBy('deleted_at', 'desc');                return $name ? $query->whereRaw('concat(name1, code1) like ?', ["%{$name}%"])                    : $query->atunit($unitcode);            })            ->paginate($limit);        return $this->layuiTableForRep($total, $page, $limit);    }复制代码