本文介绍了一种利用中间表和配置项管理复杂查询的方法，通过Laravel框架实现动态表单生成及多表关联查询。
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代码
<div class="modal inmodal" id="adv_search" tabindex="-1" role="dialog" aria-hidden="true">
<div class="modal-dialog modal-lg">
<div class="modal-content animated fadeIn">
<form class="layui-form" action="" id="js_adv_search">
<div class="modal-body">
<div class="layui-form-item layui-form-title" style="WIDTH:730px; margin-left: 55px; margin-bottom: 10px;">
<!-- 左括号开始 -->
<div class="layui-input-inline text-center" style="WIDTH:50px">
<code>左括号</code>
</div>
<!-- 左括号结束 -->
<!-- 字段选择开始 -->
<div class="layui-input-inline text-center" style="width:190px;">
<code>筛选条件</code>
</div>
<!-- 字段选择结束 -->
<!-- 比较符选择开始 -->
<div class="layui-input-inline text-center" style="width:100px;">
<code>比较符</code>
</div>
<!-- 比较符选择结束 -->
<!-- 输入字段值开始 -->
<div class="layui-input-inline text-center" id="value_html">
<code>选项或输入值</code>
</div>
<!-- 输入字段值结束 -->
<!-- 右括号开始 -->
<div class="layui-input-inline text-center" style="WIDTH:50px">
<code>右括号</code>
</div>
<!-- 右括号结束 -->
<!-- 右逻辑符开始 -->
<div class="layui-input-inline text-center" style="WIDTH:60px">
<code>关系符</code>
</div>
<div class="layui-input-inline text-center" style="WIDTH:20px">
<code>清空</code>
</div>
<!-- 右逻辑符结束 -->
</div>
<?php
$i_row_num = Setting::get("user.".Auth::id().".adv_search.row_num", 5);
for ($i=0 ; $i<$i_row_num; $i++) { ?>
<div class="layui-form-item" style="WIDTH:730px; margin-left: 55px; margin-bottom: 3px;" id="adv_search_row_{{ $i }}">
<!-- 左括号开始 -->
<div class="layui-input-inline" style="WIDTH:50px">
<input type=text name="left[]" id="left_{{ $i }}" lay-verify="left" Value="" class="layui-input" >
</div>
<!-- 左括号结束 -->
<!-- 字段选择开始 -->
<div class="layui-input-inline" style="width:190px;">
<Select name="field[]" id="field_{{ $i }}" data-fid="{{ $i }}" lay-filter="field" lay-search="">
<option value=""></option>
<?php
$c_list_tag = ['backend', 'xtgl', 'coltables'];
$c_list_name = 'b.x.coltables';
$datas = Cache::tags($c_list_tag)->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) {
?>
<option value="{{ $data->tb . '.' .$data->col }}" data-col="{{ $data->col }}" data-tb="{{ $data->tb }}" data-type="{{ $data->type }}" data-classmark="{{ $data->classmark }}">{{ $data->tbcn . '-' . $data->colcn }}</option>
<?php
}
?>
</Select>
</div>
<!-- 字段选择结束 -->
<!-- 比较符选择开始 -->
<div class="layui-input-inline" style="width:100px;">
<Select name=choose[] id="choose" lay-verify="required" lay-search="">
<option value="like" Selected>包含</option>
<option value="not like">不包含</option>
<option value="=" >等于</option>
<option value="<>" >不等于</option>
<option value=">" >大于</option>
<option value=">=" >大于等于</option>
<option value="<" >小于</option>
<option value="<=" >小于等于</option>
</Select>
</div>
<!-- 比较符选择结束 -->
<!-- 输入字段值开始 -->
<div class="layui-input-inline adv_value" id="value_html_{{ $i }}">
<input type=text name="value[{{ $i }}]" id="value_{{ $i }}" Value="" class="layui-input">
</div>
<!-- 输入字段值结束 -->
<!-- 右括号开始 -->
<div class="layui-input-inline" style="WIDTH:50px">
<input type=text name="right[]" id="right_{{ $i }}" lay-verify="right" Value="" style="WIDTH:50px" class="layui-input" >
</div>
<!-- 右括号结束 -->
<!-- 右逻辑符开始 -->
<div class="layui-input-inline" style="WIDTH:60px">
<Select name="rela[]" id="rela_{{ $i }}">
<option value="and" Selected>与</option>
<option value="or">或</option>
</Select>
</div>
<div class="layui-input-inline" style="WIDTH:20px; padding-top: 10px; padding-left: 10px;">
<a class="js-btn-action text-danger" data-type="remove" data-id="{{ $i }}" data-toggle="tooltip" title="删除本行条件"><i class="fa fa-times" aria-hidden="true"></i></a>
</div>
<!-- 右逻辑符结束 -->
</div>
<?php } ?>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-white btn-outline" data-dismiss="modal"><i class="fa fa-times"></i> 关闭</button>
<button type="button" data-modal-id="adv_search" class="btn btn-primary btn-outline do-action" data-type="adv_search_set_row"
data-action="{{ route('b.export.common_setting', ['name' => "user.".Auth::id().".adv_search.row_num"]) }}"><i class="fa fa-wrench"></i> 设置</button>
<button type="button" class="btn btn-warning btn-outline js-btn-action" data-type="reset"><i class="fa fa-refresh" aria-hidden="true"></i> 清空</button>
<button type="button" class="btn btn-primary btn-outline" lay-submit lay-filter="adv_submit"><i class="fa fa-search"></i> 查询</button>
</div>
</form>
</div>
</div>
</div>
复制代码4、adv_search_js.blade.php代码
<script type="text/javascript">
layui.use(['form', 'laydate'], function () {
var form = layui.form
,laydate = layui.laydate;
//检测field的select值变动
form.on('select(field)', function(data){
var fid = $(data.elem).data('fid'),
field = $(data.elem).find('option[value="'+ data.value +'"]'),
tb = field.data('tb'),
col = field.data('col'),
type = field.data('type'),
classmark = field.data('classmark');
//字段有对应的配置项
if (col) {
$.ajax({
url: '{{ route('b.common.classmark_html') }}',
data:{ classmark: classmark, type: type },
dataType: "text",
success: function(data) {
if(data) {
if (type == 'cn') {//可以自己输入的select
var s_html = '<Select name="value['+ fid +']" id="value_'+ fid +'" class="form-control app-editable-select" lay-ignore>' + data + '</Select>';
$('#value_html_' + fid).html(s_html);
$('.app-editable-select').editableSelect({ effects: 'slide' });
} else {
var s_html = '<Select name="value['+ fid +']" id="value_'+ fid +'" lay-verify="required" lay-search="">' + data + '</Select>';
$('#value_html_' + fid).html(s_html);
form.render('select');
}
} else {
if (type == 'date') {//时间格式
var s_html = '<input type=text name="value['+ fid +']" id="value_'+ fid +'" Value="" class="layui-input js-input-date">';
$('#value_html_' + fid).html(s_html);
lay('.js-input-date').each(function(){
laydate.render({
elem: this
,calendar: true
});
});
} else {
var s_html = '<input type=text name="value['+ fid +']" id="value_'+ fid +'" Value="" class="layui-input">';
$('#value_html_' + fid).html(s_html);
}
}
}
});
}
});
var active = {
reset: function () {
$('#js_adv_search')[0].reset();
$('.adv_value').each(function (index, domEle) {
var s_html = '<input type=text name="value['+ index +']" id="value_'+ index +'" Value="" class="layui-input">';
$(this).html(s_html);
});
},
remove: function () {
var id = $(this).data('id');
var s_html = '<input type=text name="value['+ id +']" id="value_'+ id +'" Value="" class="layui-input">';
$('#value_html_' + id).html(s_html);
$('#field_' + id).val('');
form.render('select');
}
};
//绑定操作
$('.js-btn-action').on('click', function(){
var type = $(this).data('type');
active[type] ? active[type].call(this) : '';
});
});
</script>
复制代码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);
}
复制代码
扫一扫
6004
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
