Vue动态查询条件-SQL动态查询规则-分组查询-递归组件(一):前端_马哈大哥_vue 动态查询条件

未知 02-07 7007

最近项目上有一个需求，VUE前端要实现动态查询条件组件，后端就能够动态组装SQL。

要模仿人家Azure Devops的查询功能，我丢，Azure Devops是人家微软开发的个东西

上图，这是人家 Azure Devops的，确实挺好用挺灵活~~~

这个是最终的效果：

下面是我开发的组件，跟微软的这个用起来还是有一些差别的。还需要优化。。。优化好之后再为大家更新

这最后一个图是最近优化后的，跟人家Azure Devops的功能以及展示基本差不多了。??

用起来还不错，比较完美，他有的功能，咱也都有了?

下面单独抠出来的demo，直接复制到项目中就可以使用的页面哈，有些小伙伴报错了不知道咋处理，上面都说过了，我还是帮你们改好吧。其实就是替换svg-icon这个组件为i就可以了。图片自己替换应该不用我说了吧，换class就可以了。。。

1、conditionGroup.vue

<template> <div :class="{'marginClass': onlyOne}" v-if="reDraw"> <div class="condition-header" v-if="onlyOne"> <div class="group-button"> <el-tooltip content="分组"> <i class="el-icon-edit" icon-class="group" :style="{width: groupBtnSize+'px',height: groupBtnSize+'px',color: '#409e6f',cursor: 'pointer'}" @click.stop="_addGroup"></i> </el-tooltip> </div> </div> <div v-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'}"> <i class="el-icon-circle-plus-outline color-success font-title-large" style="cursor: pointer" @click="_addItem(item)" ></i> <i class="el-icon-circle-close color-danger font-title-large" style="cursor: pointer;margin-left: 5px;" @click="_delItem(item)" ></i> <el-checkbox style="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"> <i class="el-icon-edit" icon-class="group" :style="{width: groupBtnSize+'px',height: groupBtnSize+'px',color: '#409e6f',cursor: 'pointer'}" @click="_delGroup(item,n.p)"></i> </el-tooltip> </div> </template> </div> <div style="position: relative"> <div v-if="item.header" :style="{'position': 'absolute', 'top': '-23px', 'left':'0px','display': 'flex','flex-direction': 'row', 'width': '100%'}"> <div class="condition-header" style="margin-left: calc(50% - 15px)">且/或</div> </div> <el-select v-model="item.operate" style="width: 65px;padding: 5px 0 5px 1px" size="small"> <el-option v-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"> <div v-if="item.header" :style="{'position': 'absolute', 'top': '-23px', 'left':'0px','display': 'flex','flex-direction': 'row', 'width': '100%'}"> <div class="condition-header" style="margin-left: calc(50% - 15px)">字段</div> </div> <el-select v-model="item.field" style="width: 200px; margin-left: 10px; padding: 5px 0 5px 0px" size="small" @change="item.value = '',item.condition = ''"> <el-option v-for="ot in keyList" :key="ot.val" :label="ot.key" :value="ot.val"> </el-option> </el-select> </div> <div style="position: relative"> <div v-if="item.header" :style="{'position': 'absolute', 'top': '-23px', 'left':'0px','display': 'flex','flex-direction': 'row', 'width': '100%'}"> <div class="condition-header" style="margin-left: calc(50% - 15px)">运算符</div> </div> <el-select v-model="item.condition" v-if="conditionMap && conditionMap[item.field]" style="width: 120px;margin-left: 10px; padding: 5px 0 5px 0px" size="small"> <el-option v-for="ot in conditionMap[item.field]" :key="ot.val" :label="ot.key" :value="ot.val"> </el-option> </el-select> <el-select v-model="item.condition" v-else style="width: 120px;margin-left: 10px; padding: 5px 0 5px 0px" size="small"> <el-option v-for="ot in conditionSelect" :key="ot.val" :label="ot.key" :value="ot.val"> </el-option> </el-select> </div> <div style="position: relative"> <div v-if="item.header" :style="{'position': 'absolute', 'top': '-23px', 'left':'0px','display': 'flex','flex-direction': 'row', 'width': '100%'}"> <div class="condition-header" style="margin-left: calc(10% - 15px)">值</div> </div> <el-select v-model="item.value" v-if="valList && valList[item.field] && valList[item.field].dom == 'select'" style="width: 700px;margin-left: 10px; padding: 5px 0 5px 0px" size="small"> <el-option v-for="ot in valList[item.field].data" :key="ot.val" :label="ot.key" :value="ot.val"> </el-option> </el-select> <el-date-picker v-else-if="valList && valList[item.field] && valList[item.field].dom == 'date'" size="small" v-model="item.value" type="datetime" placeholder="日期" style="width: 700px;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-input v-else v-model="item.value" style="width: 700px;margin-left: 10px; padding: 5px 0 5px 0px" placeholder="值" clearable size="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-button v-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(64, 158, 111, '+alpha+')'] } }, data() { return { plotList: [], loading: false, reDraw: true, addGroupIndex: 0, // borderColor: ['#ffeb3b', '#4caf50', '#ffc107', '#ff9800', '#ff5722', '#2196f3', '#673ab7', '#795548', '#009688', '#009688', '#607d8b', '#9e9e9e', '#926012'], conditionSelect: [ {'key': '>', 'val': 'gt'}, {'key': '=', 'val': 'eq'}, {'key': '<', 'val': 'lt'}, {'key': '>=', 'val': 'gtq'}, {'key': '<=', 'val': 'ltq'}, {'key': '包含', 'val': 'like'}, {'key': '不包含', 'val': 'not like'} ] } }, 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 { --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>

2、使用的这个组件的demo.vue

<template> <div> <conditionGroup :floor="floor" :conditionList="conditionList" :parentData="this" :key-list="keyOptions" :condition-map="conditionOptions" :val-list="valueOptions"> </conditionGroup> <el-button type="primary" @click="query">查询</el-button> </div> </template> <script> import conditionGroup from "./conditionGroup"; const condition = { id: 1, index: 1, condition: '', operate: 'and', field: '', value: '', header: true, checked: false, pid: -1, floor: 1 } export default { name: 'demo', components: { conditionGroup }, data() { return { conditionList: [ Object.assign({}, condition) ], floor: 1, keyOptions: [ {'key': 'ID', 'val': 'id'}, {'key': '名称', 'val': 'name'}, {'key': '类型', 'val': 'type'}, {'key': '来源', 'val': 'source'}, {'key': '昵称', 'val': 'nick_name'}, {'key': '创建时间', 'val': 'create_time'}, {'key': '更新时间', 'val': 'update_time'}, ], conditionOptions: { type: [ {'key': '=', 'val': 'eq'}, ], source: [ {'key': '=', 'val': 'eq'}, ] }, valueOptions: { type : { dom: 'select', data: [ {'key': 'time', 'val': 'time'}, {'key': 'date', 'val': 'date'}, ] }, source : { dom: 'select', data: [ {'key': 'm1', 'val': 'm'}, {'key': 'a1', 'val': 'a'}, ] }, create_time : { dom: 'date' }, update_time : { dom: 'date' }, } } }, created() { }, methods: { query(){ console.log(this.conditionList) } } } </script> <style scoped> </style>

这样前端就完成了，当然还需要处理这些条件哈，后端才能组装成可用的sql。

可能有些小伙伴处理这个又有麻烦啦。。。这个自己动动脑子吧，一样的，迭代一下，组装成sql就可以啦~~~~

前端用这个组件的话，前后端都很方便了，只要是单表展示或者不太复杂的多表，都可以通用一个后端的哦。 ?

前端提交给后端一个tableName和conditionList就可以了

后端只需要组装sql，返回结果。这个后端通用的哦，不需要为每个页面也就是每个表单独去做后端。

大家给个关注+点赞+收藏哈~~~ 应大家的要求，后续更新后端如何处理~~~ 整体来讲，这个前端加后端，是非常通用的，用于生产系统完全没有问题，好用又省事~ Vue动态查询条件-SQL动态查询规则-分组查询-递归组件(二):后端https://blog.csdn.net/duanjunkaisky/article/details/126298636