vue配置sql规则

前端页面实现动态配置sql条件，将JSON结构给到后端，后端进行sql组装。
这里涉及的分组后端在组装时用括号将这块规则括起来就行，分组的sql连接符（并且/或者）取组里的第一个。

实现效果

组件完整代码

<!-- conditionGroup.vue -->
<template><div :class="{ marginClass: onlyOne }" v-if="reDraw"><div class="condition-header" v-if="onlyOne"><div class="group-button"><el-tooltip content="分组"><iclass="el-icon-folder-opened"icon-class="group":style="{width: groupBtnSize + 'px',height: groupBtnSize + 'px',color: '#1890ff',cursor: 'pointer',}"@click.stop="_addGroup"></i></el-tooltip></div></div><divv-for="(item, index) in conditionList":style="{ 'flex-direction': 'column' }"><div:style="{display: 'flex','flex-direction': 'row','align-items': 'center',}"v-if="!item.groups"><div:style="{display: 'flex','flex-direction': 'row','align-items': 'center',}"><iclass="el-icon-circle-plus-outline color-success font-title-large"style="cursor: pointer"@click="_addItem(item)"></i><iclass="el-icon-circle-close color-danger font-title-large"style="cursor: pointer; margin-left: 5px"@click="_delItem(item)"></i><el-checkboxstyle="padding: 0 10px 0 10px"v-model="item.checked"></el-checkbox><template v-if="floor > 1 && (!item.line || item.line.length == 0)"><div:style="{width: gradWidth + leftWidth * (floor - item.floor - 1) + 'px',height: '42px',}"></div></template><template v-else v-for="(n, li) in item.line"><div:style="{width:li == item.line.length - 1? gradWidth + leftWidth * (floor - item.floor) + 'px': leftWidth + 'px',height: '42px',background: getFloorColor(li + 1),}":class="{'group-left': n.l == 2,'group-top-left': n.l == 4,'group-bottom-left': n.l == 5,}"><el-tooltip :content="'点击取消所在分组'" v-if="n.l == 4"><iclass="el-icon-folder-opened"icon-class="group":style="{width: groupBtnSize + 'px',height: groupBtnSize + 'px',color: '#1890ff',cursor: 'pointer',}"@click="_delGroup(item, n.p)"></i></el-tooltip></div></template></div><div style="position: relative"><divv-if="item.header":style="{position: 'absolute',top: '-23px',left: '0px',display: 'flex','flex-direction': 'row',width: '100%',}"><div class="condition-header" style="margin-left: 20px">并且/或者</div></div><el-selectv-model="item.operate"style="width: 120px; padding: 5px 0 5px 1px"size="small":disabled="item.header"><el-optionv-for="ot in [{ key: '并且', val: 'and' },{ key: '或者', val: 'or' },]":key="ot.val":label="ot.key":value="ot.val"></el-option></el-select></div><div style="position: relative"><divv-if="item.header":style="{position: 'absolute',top: '-23px',left: '0px',display: 'flex','flex-direction': 'row',width: '100%',}"><div class="condition-header" style="margin-left: 20px">字段</div></div><el-selectv-model="item.field"style="width: 200px; margin-left: 10px; padding: 5px 0 5px 0px"size="small"@change="(item.value = ''), (item.condition = '')"><el-optionv-for="ot in keyList":key="ot.val":label="ot.key":value="ot.val"></el-option></el-select></div><div style="position: relative"><divv-if="item.header":style="{position: 'absolute',top: '-23px',left: '0px',display: 'flex','flex-direction': 'row',width: '100%',}"><div class="condition-header" style="margin-left: 20px">运算符</div></div><el-selectv-model="item.condition"v-if="conditionMap && conditionMap[item.field]"style="width: 120px; margin-left: 10px; padding: 5px 0 5px 0px"size="small"><el-optionv-for="ot in conditionMap[item.field]":key="ot.val":label="ot.key":value="ot.val"></el-option></el-select><el-selectv-model="item.condition"v-elsestyle="width: 120px; margin-left: 10px; padding: 5px 0 5px 0px"size="small"><el-optionv-for="ot in conditionSelect":key="ot.val":label="ot.key":value="ot.val"></el-option></el-select></div><div style="position: relative"><divv-if="item.header":style="{position: 'absolute',top: '-23px',left: '0px',display: 'flex','flex-direction': 'row',width: '100%',}"><div class="condition-header" style="margin-left: 20px">值</div></div><!-- 值类型：下拉选项、日期、时间、小数、整数、文本框 --><el-selectv-model="item.value"v-if="valList &&valList[item.field] &&valList[item.field].dom == 'select'"style="width: 400px; margin-left: 10px; padding: 5px 0 5px 0px"size="small"placeholder="请选择"><el-optionv-for="ot in valList[item.field].data":key="ot.dictValue":label="ot.dictLabel":value="ot.dictValue"></el-option></el-select><el-date-pickerv-else-if="valList &&valList[item.field] &&valList[item.field].dom == 'date'"size="small"v-model="item.value"type="date"placeholder="请选择日期"style="width: 400px;margin-left: 10px;cursor: pointer;padding: 5px 0 5px 0px;":editable="false"format="yyyy-MM-dd"value-format="yyyy-MM-dd"></el-date-picker><el-date-pickerv-else-if="valList &&valList[item.field] &&valList[item.field].dom == 'dateTime'"size="small"v-model="item.value"type="datetime"placeholder="请选择时间"style="width: 400px;margin-left: 10px;cursor: pointer;padding: 5px 0 5px 0px;":editable="false"format="yyyy-MM-dd HH:mm:ss"value-format="yyyy-MM-dd HH:mm:ss"></el-date-picker><el-inputv-else-if="valList &&valList[item.field] &&valList[item.field].dom == 'decimals'"v-model="item.value"style="width: 400px; margin-left: 10px; padding: 5px 0 5px 0px"placeholder="请输入(支持小数)"clearabletype="number"size="small"oninput="if(value < 0 || value == '' || value == null) value='';"/><!-- if(!/^\d+(\.\d{1,2})?$/.test(value)) value=value.match(/[\d]+(\.\d{0,2})?/)?.[0];  两位 --><el-inputv-else-if="valList &&valList[item.field] &&valList[item.field].dom == 'integer'"v-model="item.value"style="width: 400px; margin-left: 10px; padding: 5px 0 5px 0px"placeholder="请输入(整数)"clearabletype="number":min="1"size="small"oninput="if(value.includes('.')) value=value.replace(/\./g, ''); if(value < 0 || value == '' || value == null) value=''; if(!/^\d+$/.test(value)) value=value.replace(/\D/g,'');"/><el-inputv-elsev-model="item.value"style="width: 400px; margin-left: 10px; padding: 5px 0 5px 0px"placeholder="请输入"clearablesize="small"/></div></div><conditionGroup:conditionList="item.groups"v-if="item.groups && item.groups.length > 0":only-one="false":parentData="parentData":floor="floor":borderColor="borderColor":key-list="keyList":val-list="valList":condition-map="conditionMap"></conditionGroup></div><!-- <el-buttonv-if="onlyOne"size="small"style="margin-top: 10px; cursor: pointer"@click="_addChild">添加规则</el-button> --></div>
</template><script>
const condition = {id: 1,index: 1,condition: "",operate: "and",field: "",value: "",checked: false,header: true,pid: -1,floor: 1,
};const gradWidth = 20;
const leftWidth = 20;
const groupBtnSize = 20;
const alpha = 0.2;const initData = [Object.assign({}, condition)];export default {name: "conditionGroup",components: {},props: {onlyOne: {type: Boolean,default: () => true,},floor: {type: Number,default: () => 1,},conditionList: {type: Array,default: () => initData,},keyList: {type: Array,default: () => [],},conditionMap: {type: Object,default: () => {},},valList: {type: Object,default: () => {},},parentData: {type: Object,default: () => {},},gradWidth: {type: Number,default: () => gradWidth,},leftWidth: {type: Number,default: () => leftWidth,},groupBtnSize: {type: Number,default: () => groupBtnSize,},borderColor: {type: Array,default: () => ["rgba(24, 124, 255, " + alpha + ")"],},},data() {return {plotList: [],loading: false,reDraw: true,addGroupIndex: 0,conditionSelect: [{ key: "等于", val: "eq" },{ key: "不等于", val: "notEq" },{ key: "大于", val: "gt" },{ key: "小于", val: "lt" },{ key: "大于等于", val: "gtq" },{ key: "小于等于", val: "ltq" },{ key: "包含", val: "like" },{ key: "不包含", val: "notLike" },// { key: "加", val: "add" },// { key: "减", val: "subtract" },// { key: "乘", val: "multiply" },// { key: "除", val: "divide" },],};},computed: {sidebar() {return this.$store.state.app.sidebar.opened;},},watch: {conditionList(val, oldVal) {this.$emit("input", val);while (this.borderColor.length < this.floor) {var _color = this.randomHexColor();while (this.borderColor.indexOf(_color) != -1) {_color = this.randomHexColor();}this.borderColor.push(_color);}this.reDraw = false;this.$nextTick(() => {this.reDraw = true;});},sidebar(val) {},},methods: {findChecked(list, arrParam) {var arr = arrParam || new Array();for (var i = 0; i < list.length; i++) {var o = list[i];if (o.groups && o.groups.length > 0) {this.findChecked(o.groups, arr);} else {if (o.checked) {arr.push(o);}}}return arr;},removeNode(list, targetList) {for (var i = 0; i < list.length; i++) {var o = list[i];for (var tid of targetList) {if (o.id == tid) {list.splice(i--, 1);}}}},findParentGroups(list, pid, retParam) {var ret = null || retParam;for (var i = 0; i < list.length; i++) {var o = list[i];if (o.groups && o.groups.length > 0) {if (o.id == pid) {ret = o;} else {ret = this.findParentGroups(o.groups, pid, ret);}}}return ret;},_addGroup() {this.addGroup(this.parentData.conditionList, this.parentData);},_delGroup(item, groupId) {this.delGroup(groupId, this.parentData.conditionList, this.parentData);},_addChild() {this.addChild(this.parentData.conditionList);},_delItem(item) {this.delItem(this.conditionList,item,this.parentData.conditionList,this.parentData);},_addItem(item) {this.addItem(this.conditionList,item.index,this.parentData.conditionList,this.parentData);},addItem(groups, index, conditionList, parentThis) {var newItem = Object.assign({}, condition, {id: new Date().getTime(),index: index + 1,floor: groups[0].floor,pid: groups[0].pid,});groups.splice(index, 0, newItem);parentThis.floor = this.refreshData(conditionList);},addChild(conditionList) {var newItem = Object.assign({}, condition, {id: new Date().getTime(),index: conditionList.length + 1,floor: 1,pid: -1,});newItem.header = false;conditionList.splice(conditionList.length, 0, newItem);},delItem(groups, item, conditionList, parentThis) {var sum = this.countItem(conditionList);if (sum <= 1) {return;}groups.splice(item.index - 1, 1);var currentGroups = this.findParentGroups(conditionList, groups[0].pid);if (currentGroups) {var parentGroups = this.findParentGroups(conditionList,currentGroups.pid);if (currentGroups.groups.length == 1) {var ag = JSON.parse(JSON.stringify(currentGroups.groups[0]));ag.index = currentGroups.index;ag.id = currentGroups.id;ag.pid = parentGroups ? parentGroups.id : -1;ag.floor = currentGroups.floor;if (ag.groups) {ag.groups.forEach((o, index) => {o.pid = ag.id;o.floor = ag.floor + 1;o.index = index + 1;});}if (parentGroups) {var _groups = this.findParentGroups(conditionList, parentGroups.id);_groups.groups.splice(currentGroups.index - 1, 1, ag);} else {conditionList.splice(currentGroups.index - 1, 1, ag);}}}if (conditionList.length == 1 && conditionList[0].groups) {var newList = JSON.parse(JSON.stringify(conditionList[0].groups));conditionList.splice(0, 1);for (var nl of newList) {nl.pid = -1;nl.floor = 1;conditionList.push(nl);}}parentThis.floor = this.refreshData(conditionList);},addGroup(conditionList, parentThis) {var checkedList = this.findChecked(conditionList);if (!checkedList || checkedList.length <= 1) {this.$message({message: "至少选择2个查询条目",type: "warning",duration: 1000,});return;}var checkNodes = [];for (var item of checkedList) {if (item.pid == -1) {this.uniquePush(checkNodes, item);} else {var pNode = this.getRealParent(conditionList, item, checkedList);if (pNode) {this.uniquePush(checkNodes, pNode);}}}var _tmpRoot = [];for (var ck of checkNodes) {var _tmp = this.findParentGroups(conditionList, ck.pid);if (_tmp) {this.uniquePush(_tmpRoot, _tmp);}}var allSelectCount = 0;var floorCount = [];for (var cn of checkNodes) {if (cn.groups) {allSelectCount += this.countItem(cn.groups);} else {allSelectCount++;}if (floorCount.indexOf(cn.floor) == -1) {floorCount.push(cn.floor);}}var rootGroup = this.findParentGroups(conditionList, checkNodes[0].pid);if (_tmpRoot.length > 1) {rootGroup = this.findParentGroups(conditionList, rootGroup.pid);allSelectCount = 0;for (var cn of _tmpRoot) {if (cn.groups) {allSelectCount += this.countItem(cn.groups);} else {allSelectCount++;}}}var rootArray = conditionList;if (rootGroup) {rootArray = rootGroup.groups;}var allCount = this.countItem(rootArray);var currentSelectCount = checkedList.length;if (allSelectCount != currentSelectCount || floorCount.length > 1) {this.$message({message: "不能交叉分组",type: "warning",duration: 1000,});return;}if (checkNodes.length == 1 || allCount == currentSelectCount) {this.$message({message: "无效分组",type: "warning",duration: 1000,});return;}var newCheckNode = JSON.parse(JSON.stringify(checkNodes));newCheckNode.sort(function (a, b) {return a.index - b.index;});var groupId = new Date().getTime();var newGroup = {groups: newCheckNode,id: groupId,index: newCheckNode[0].index,pid: newCheckNode[0].pid,floor: newCheckNode[0].floor,};var waitRemoveNode = [];for (var o of newCheckNode) {o.floor += 1;o.pid = groupId;if (!o.groups) {o.checked = false;}waitRemoveNode.push(o.id);}if (!rootGroup) {this.removeNode(conditionList, waitRemoveNode);conditionList.splice(newCheckNode[0].index - 1, 0, newGroup);} else {var _groups = this.findParentGroups(conditionList, rootGroup.id);this.removeNode(_groups.groups, waitRemoveNode);_groups.groups.splice(newCheckNode[0].index - 1, 0, newGroup);}parentThis.floor = this.refreshData(conditionList);},delGroup(groupId, conditionList, parentThis) {var parentGroups = this.findParentGroups(conditionList, groupId);var rootGroups = this.findParentGroups(conditionList, parentGroups.pid);var waitRemoveNode = [parentGroups.id];var newList = JSON.parse(JSON.stringify(parentGroups.groups));newList.forEach((o, index) => {o.pid = parentGroups.pid;o.floor = parentGroups.floor;o.checked = false;});if (!rootGroups) {this.removeNode(conditionList, waitRemoveNode);newList.forEach((o, index) => {conditionList.splice(parentGroups.index - 1 + index, 0, o);});} else {var _groups = this.findParentGroups(conditionList, rootGroups.id);this.removeNode(_groups.groups, waitRemoveNode);newList.forEach((o, index) => {_groups.groups.splice(parentGroups.index - 1 + index, 0, o);});}parentThis.floor = this.refreshData(conditionList);},getRealParent(allItems, item, checkedList) {var parentGroups = this.findParentGroups(allItems, item.pid);var ret = parentGroups;if (parentGroups) {var childCount = this.countItem(parentGroups.groups);var realChildCount = 0;for (var cl of checkedList) {if (cl.pid == parentGroups.id) {realChildCount++;} else {var pg = this.findParentGroups(allItems, cl.pid);if (pg) {if (pg.pid == parentGroups.id) {realChildCount++;} else {while (pg && pg.pid != parentGroups.id) {pg = this.findParentGroups(allItems, pg.pid);if (pg && pg.pid == parentGroups.id) {realChildCount++;}}}}}}if (childCount == realChildCount) {var _tmp = this.getRealParent(allItems, parentGroups, checkedList);if (_tmp) {ret = _tmp;}} else {ret = item;}}return ret;},reIndex(list, i, arr) {for (var index = 0; index < list.length; index++) {var o = list[index];if (arr.indexOf(i) == -1) {arr.push(i);}if (o.groups && o.groups.length > 0) {o.index = index + 1;o.floor = i;if (i == 1) {o.pid = -1;}this.reIndex(o.groups, i + 1, arr);} else {o.index = index + 1;o.floor = i;o.checked = false;if (i == 1) {o.pid = -1;}}}},drawLineGroup(list, currentFloor, retList) {for (var index = 0; index < list.length; index++) {var o = list[index];if (o.groups && o.groups.length > 0) {this.drawLineGroup(o.groups, currentFloor + 1, retList);} else {o.line = new Array(currentFloor - 1);if (retList.length == 0) {o.header = true;} else {o.header = false;}for (var _k = 0; _k < o.line.length; _k++) {o.line[_k] = { l: 2, p: -1 };}retList.push(o);}}},refreshData(list) {var floorCountArr = [];this.reIndex(list, 1, floorCountArr);var maxFloor = floorCountArr.length;var ret = new Array();this.drawLineGroup(list, 1, ret);for (var item of ret) {var parentGroup = this.findParentGroups(list, item.pid);if (item.pid != -1) {if (item.index == 1) {var node = { l: 4, p: parentGroup.id };item.line[item.line.length - 1] = node;} else if (item.index == parentGroup.groups.length) {var node = { l: 5, p: -1 };item.line[item.line.length - 1] = node;}}if (parentGroup) {var parentIndex = parentGroup.index;var parentLength = parentGroup.groups.length;var i = 2;var currentParentGroup = this.findParentGroups(list, parentGroup.pid);while (currentParentGroup) {if (i != 2) {parentGroup = JSON.parse(JSON.stringify(currentParentGroup));currentParentGroup = this.findParentGroups(list, parentGroup.pid);}if (currentParentGroup) {if (parentGroup.index == 1 &&item.index == 1 &&parentIndex == 1) {var node = { l: 4, p: currentParentGroup.id };item.line[item.line.length - i] = node;} else if (parentGroup.index == currentParentGroup.groups.length &&item.index == parentLength) {item.line[item.line.length - i] = { l: 5, p: -1 };} else {break;}i++;}}}}return maxFloor;},countItem(list, i) {var sum = i || 0;for (var index = 0; index < list.length; index++) {var o = list[index];if (o.groups && o.groups.length > 0) {sum += this.countItem(o.groups, i);} else {sum++;}}return sum;},uniquePush(arr, item) {var exist = false;for (var o of arr) {if (o.id == item.id) {exist = true;}}if (!exist) {arr.push(item);}},randomHexColor() {// return '#' + ('00000' + (Math.random() * 0x1000000 << 0).toString(16)).substr(-6);return this.randomColor(alpha);},randomColor(alpha) {alpha =alpha == undefined ? ((Math.random() * 10) / 10).toFixed(1) : alpha;alpha = Number(alpha);if (isNaN(alpha)) alpha = 1;var col = "rgba(";for (var i = 0; i < 3; i++) {col += parseInt(Math.random() * 256) + ",";}col += alpha + ")";return col;},getFloorColor(floor) {return this.borderColor[floor - 1];},},created() {if (typeof this.conditionList[0].field == "string" &&typeof this.conditionList[0].header == "undefined") {this.conditionList[0].header = true;}this.$nextTick(() => {});},
};
</script><style type="text/css">
/* :root 是CSS中的一个伪类选择器，它代表文档的根元素。在HTML中，根元素通常是<html>标签。使用:root选择器允许开发者为整个文档定义全局样式变量
--borderWidth：这是一个自定义的CSS变量
使用var(--variableName)语法进行引用 */:root {--borderWidth: 1px;--borderColor: rgba(158, 158, 158, 1);
}table {border-collapse: collapse;
}.marginClass {margin-bottom: 10px;
}.condition-header {font-weight: 600;display: flex;flex-direction: row;
}.group-button {margin-left: 47px;display: flex;flex-direction: row;align-items: center;
}.group-left {border-left: var(--borderWidth) solid var(--borderColor);
}
.group-top-left {border-top: var(--borderWidth) solid var(--borderColor);border-left: var(--borderWidth) solid var(--borderColor);
}
.group-bottom-left {border-bottom: var(--borderWidth) solid var(--borderColor);border-left: var(--borderWidth) solid var(--borderColor);
}
</style>

父组件

<template><!-- :parentData="this"传递父组件实例，子组件使用属性或方法时this.parentData.属性/方法名 -->
<conditionGroup:floor="floor":conditionList="conditionList":parentData="this":key-list="keyOptions":condition-map="conditionOptions":val-list="valueOptions"></conditionGroup>
</template><sctipt>
export default {data() {conditionList: [Object.assign({}, condition)], // 默认页面显示一条空规则floor: 1, // 树深度keyOptions: [], // 字段选项conditionOptions: {}, // 特殊运算符（像下拉框的只有 等于 运算符等）valueOptions: {}, //下拉框类型的数据},// 一些关键方法methods: {// 递归找结构深度getFloorth(groups) {let deepestFloor = 0;let deepestGroup = null;function searchGroup(group) {// 找到比当前deepestFloor值大，则重新赋值deepestFloor，并将当前对象记录if (group.floor > deepestFloor) {deepestFloor = group.floor;deepestGroup = group;}if (group.groups && group.groups.length > 0) {group.groups.forEach(searchGroup);}}groups.forEach(searchGroup);return deepestGroup ? deepestGroup.floor : 1;},// 提交时校验规则是否有未填/选项traverse(data) {for (let item of data) {// 这里需要判空的参数field、condition、value，如果有为空则提示，不可保存if (!item.groups &&(!item?.field || !item?.condition || !item?.value)) {this.$message.warning("请完善规则条件配置");// 当校验失败时抛出错误，`中断当前执行的代码块`，并被上层catch语句捕获throw new Error("字段验证失败"); } else if (item.groups) {this.traverse(item.groups);}}return true;},// 根据其他下拉框，查询动态字段项selectChange(obj) {let { name, val } = obj;if (name === "equipType") {this.keyOptions = [];this.valueOptions = {};this.conditionOptions = {};let keyOptionsDict = val + "_column"; // keyOptionsDict 为val和_column拼接的字典名称去过滤对应字典项let options = this.$options.filters["dictOption"]({dictName: keyOptionsDict,});// 有过滤到字段项，给字段项字段push数据（字段key和val）if (options.length) {options.forEach((item) => {// item.dictValue用#拼接的三个参数，如heat_status_code#select#heat_status（第一个为规则字段键、第二个为值类型、第三个为字典名）let parts = item.dictValue.split("#");this.keyOptions.push({key: item.dictLabel,val: parts[0],});if (parts[1] === "select") {// 如果是select下拉框，则拼接下拉框值数据项（我们这块都是匹配的字典查询，可拓展为接口查询）this.valueOptions[parts[0]] = {dom: parts[1],data: this.$options.filters["dictOption"]({dictName: parts[2],}),};// 若是下拉框，处理运算符this.conditionOptions[parts[0]] = [{ key: "等于", val: "eq" }];} else {// 其他字段直接给dom属性this.valueOptions[parts[0]] = { dom: parts[1] };}});} else {this.conditionList = [Object.assign({}, condition)];this.keyOptions = [];this.conditionOptions = {};}}},// 若需要前端组装给后端可使用下面方法（我这里是直接给的JSON，前端不进行组装）generateConditionString(conditionList) {let result = "";if (conditionList.length === 0) return result;const generateSubConditions = (conditions) => {return conditions.map((condition, index) => {if (condition.groups && condition.groups.length > 0) {let connectSym = condition.groups[0].operate;return index === 0? `(${generateSubConditions(condition.groups)})`: ` ${connectSym} (${generateSubConditions(condition.groups)})`;}let value = condition.value;if (typeof value === "string") {if (condition.condition === "like" ||condition.condition === "notLike") {value = `'%${value.replace(/'/g, "")}%'`;} else {value = `'${value.replace(/'/g, "''")}'`;}}if (!condition?.field ||!condition?.condition ||!condition?.value) {this.$message.warning("请完善规则条件配置");return result;}const conditionString = `${condition.field} ${this.operators[condition.condition]} ${value}`;const logicOperator = ` ${condition.operate} `;return `${index === 0 ? "" : logicOperator}${conditionString}`;}).join("");};result = generateSubConditions(conditionList);this.querystring = result;return result;},}
}
</script>

参考文章