Overview
Comment: | Query builder: fix issues with IN operator |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | 7.3 |
Files: | files | file ages | folders |
SHA1: |
9be40e9fa2be72280c479f2577d4f7d5 |
User & Date: | bohwaz on 2020-11-11 21:57:36 |
Other Links: | branch diff | manifest | tags |
Context
2020-11-13
| ||
02:24 | Plot: Improve plot axis check-in: fc76ab5c45 user: bohwaz tags: 7.3 | |
2020-11-11
| ||
21:57 | Query builder: fix issues with IN operator check-in: 9be40e9fa2 user: bohwaz tags: 7.3 | |
14:49 | SVG Plot: improve Y-axis values check-in: eddf009020 user: bohwaz tags: 7.3 | |
Changes
Modified src/js/sql_query_builder/query_builder.js from [4c4fb89305] to [e178ffeb5b].
︙ | ︙ | |||
188 189 190 191 192 193 194 | if (row.parentNode.childNodes.length <= 1) return; row.parentNode.removeChild(row); } qb.prototype.switchColumn = function (columnSelect) { var row = columnSelect.parentNode.parentNode; row.childNodes[2].innerHTML = ''; | < | 188 189 190 191 192 193 194 195 196 197 198 199 200 201 | if (row.parentNode.childNodes.length <= 1) return; row.parentNode.removeChild(row); } qb.prototype.switchColumn = function (columnSelect) { var row = columnSelect.parentNode.parentNode; row.childNodes[2].innerHTML = ''; if (!columnSelect.value) { return; } // Select first operator |
︙ | ︙ | |||
228 229 230 231 232 233 234 | return select; }; qb.prototype.switchOperator = function (operatorSelect, values) { var row = operatorSelect.parentNode.parentNode; | | | > | | | 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 | return select; }; qb.prototype.switchOperator = function (operatorSelect, values) { var row = operatorSelect.parentNode.parentNode; if (!values && row.childNodes[3].firstChild) { values = [row.childNodes[3].firstChild.value]; } row.childNodes[3].innerHTML = ''; var parent = row.childNodes[3]; var columnSelect = row.childNodes[1].firstChild; var operator = operatorSelect.value; var column = this.columns[columnSelect.value]; if (!operator) { return; } var number = 1; var buttons = false; var prev = null; var params = operator.match(/\?/g); if (params && operator.match(/\?\?/)) { number = values ? values.length : 1; buttons = true; } else if (params && params.length >= 1) { number = params.length; } else if (column.type == 'bitwise' && operator == '&') |
︙ | ︙ | |||
435 436 437 438 439 440 441 | }; if (!row.operator) { continue; } | < < < < < < | 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 | }; if (!row.operator) { continue; } conditions.push(row); } groups.push({ "operator": g.parentNode.firstChild.firstChild.value, "conditions": conditions, }); } return groups; }; }()); |
Modified src/js/sql_query_builder/query_builder.min.js from [f2e9e173b4] to [a0102fba69].
|
| | | 1 | !function(){var e=function(e){this.columns=e};(window.SQLQueryBuilder=e).prototype.loadDefaultOperators=function(){for(var e in this.operators={"= ?":this.__("is equal to"),"!= ?":this.__("is not equal to"),"IN (??)":this.__("is equal to one of"),"NOT IN (??)":this.__("is not equal to one of"),"> ?":this.__("is greater than"),">= ?":this.__("is greater than or equal to"),"< ?":this.__("is less than"),"<= ?":this.__("is less than or equal to"),"BETWEEN ? AND ?":this.__("is between"),"NOT BETWEEN ? AND ?":this.__("is not between"),"IS NULL":this.__("is null"),"IS NOT NULL":this.__("is not null"),"LIKE ?%":this.__("begins with"),"NOT LIKE ?%":this.__("doesn't begin with"),"LIKE %?":this.__("ends with"),"NOT LIKE %?":this.__("doesn't end with"),"LIKE %?%":this.__("contains"),"NOT LIKE %?%":this.__("doesn't contain"),"&":this.__("matches one of"),"= 1":this.__("is true"),"= 0":this.__("is false")},this.types_operators={integer:["= ?","!= ?","IN (??)","NOT IN (??)","> ?",">= ?","< ?","<= ?","BETWEEN ? AND ?","NOT BETWEEN ? AND ?"],enum:["= ?","!= ?","IN (??)","NOT IN (??)"],boolean:["= 1","= 0"],text:["= ?","!= ?","IN (??)","NOT IN (??)","LIKE ?%","NOT LIKE ?%","LIKE %?","NOT LIKE %?","LIKE %?%","NOT LIKE %?%"],bitwise:["&"]},this.types_operators){var t={};for(var i in this.types_operators[e]){var o=this.types_operators[e][i];t[o]=this.operators[o]}this.types_operators[e]=t}this.types_operators.date=JSON.parse(JSON.stringify(this.types_operators.integer)),delete this.types_operators.date["<= ?"],delete this.types_operators.date[">= ?"],this.types_operators.date["< ?"]=this.__("before"),this.types_operators.date["> ?"]=this.__("after"),this.types_operators.datetime=this.types_operators.date},e.prototype.__=function(e){return e},e.prototype.init=function(e){this.parent=e;var t={"":"---"};for(column in this.columns)t[column]=this.columns[column].label;this.columnSelect=this.buildSelect(t)},e.prototype.addGroup=function(t,e){var i=document.createElement("fieldset"),o=document.createElement("legend"),n=this.buildSelect({AND:this.__("Matches ALL of the following conditions:"),OR:this.__("Matches ANY of the following conditions:"),ADD:this.__("Add a new set of conditions below this one"),DEL:this.__("Remove this set of conditions")});n.onfocus=function(){this.oldValue=this.value},n.value=e;var r=this;n.onchange=function(){if("DEL"==this.value){if(1==t.childNodes.length)return void(this.value=this.oldValue);t.removeChild(i)}else if("ADD"==this.value){var e=r.addGroup(t,"AND");r.addRow(e),this.value=this.oldValue}},o.appendChild(n),i.appendChild(o);var s=document.createElement("table");return i.appendChild(s),t.appendChild(i),i},e.prototype.addRow=function(e,t){var i=e.getElementsByTagName("table")[0],o=document.createElement("tr");(s=document.createElement("td")).className="buttons";var n,r=this;(n=this.buildInput("button","+")).onclick=function(){r.addRow(function(e,t){for(;(e=e.parentElement)&&!(e.matches||e.matchesSelector).call(e,t););return e}(this,"fieldset"),this.parentNode.parentNode)},s.appendChild(n),(n=this.buildInput("button","-")).onclick=function(){r.deleteRow(this.parentNode.parentNode)},s.appendChild(n),o.appendChild(s),(s=document.createElement("td")).className="column";var s,l=this.columnSelect.cloneNode(!0);return l.onchange=function(){return r.switchColumn(this)},s.appendChild(l),o.appendChild(s),(s=document.createElement("td")).className="operator",o.appendChild(s),(s=document.createElement("td")).className="values",o.appendChild(s),void 0===t?i.appendChild(o):i.insertBefore(o,t.nextSibling),o},e.prototype.deleteRow=function(e){e.parentNode.childNodes.length<=1||e.parentNode.removeChild(e)},e.prototype.switchColumn=function(e){var t=e.parentNode.parentNode;if(t.childNodes[2].innerHTML="",e.value){var i=this.addOperator(t,this.columns[e.value]);i.value=i.children[1].value,this.switchOperator(i,null)}},e.prototype.addOperator=function(e,t){var i=this.types_operators[t.type],o={"":"---"};for(var n in t.null&&(i["IS NULL"]=this.operators["IS NULL"],i["IS NOT NULL"]=this.operators["IS NOT NULL"]),i)o[n]=i[n];var r=this.buildSelect(o),s=this;return r.onchange=function(){return s.switchOperator(this)},e.childNodes[2].appendChild(r),r},e.prototype.switchOperator=function(e,t){var i=e.parentNode.parentNode;!t&&i.childNodes[3].firstChild&&(t=[i.childNodes[3].firstChild.value]),i.childNodes[3].innerHTML="";var o=i.childNodes[3],n=i.childNodes[1].firstChild,r=e.value,s=this.columns[n.value];if(r){var l=1,a=!1,d=null,h=r.match(/\?/g);if(h&&r.match(/\?\?/))l=t?t.length:1,a=!0;else if(h&&1<=h.length)l=h.length;else{if("bitwise"!=s.type||"&"!=r)return;l=1}for(var u=0;u<l;u++)if(d=this.addMatchField(o,d,s,r),"bitwise"==s.type&&t)for(var p=0;p<s.values.length;p++)o.querySelectorAll("input")[p].checked=-1!=t.indexOf(p.toString());else t&&(d.value=t[u]);if(a){(c=this.buildInput("button","-")).onclick=function(){this.parentNode.childNodes.length<=3||(this.parentNode.removeChild(this.previousSibling),this.parentNode.removeChild(this.previousSibling))},o.appendChild(c);var c=this.buildInput("button","+"),f=this;c.onclick=function(){f.addMatchField(o,this.previousSibling.previousSibling,s,r)},o.appendChild(c)}}},e.prototype.addMatchField=function(e,t,i,o){if("enum"==i.type)var n=this.buildSelect(i.values);else if("bitwise"==i.type){n=document.createElement("span");for(var r in i.values){var s=this.buildInput("checkbox",r),l=document.createElement("label");l.appendChild(s),l.appendChild(document.createTextNode(" "+i.values[r])),n.appendChild(l.cloneNode(!0))}}else n=this.buildInput(i.type,"",i);return n=e.insertBefore(n,t?t.nextSibling:null),t&&e.insertBefore(document.createElement("br"),n),n},e.prototype.buildInput=function(e,t,i){var o=document.createElement("input");return o.type="integer"==e?"number":e,o.value=t,o},e.prototype.buildSelect=function(e){var t=document.createElement("select");for(var i in e){var o=document.createElement("option");o.value=i,o.innerHTML=e[i],t.appendChild(o)}return t},e.prototype.import=function(e){for(var t in e)if(0!=e[t].conditions.length){var i=this.addGroup(this.parent,e[t].operator);for(var o in e[t].conditions){var n=e[t].conditions[o],r=this.addRow(i);r.childNodes[1].firstChild.value=n.column;var s=this.addOperator(r,this.columns[n.column]);s.value=n.operator,this.switchOperator(s,n.values)}}},e.prototype.export=function(){var e=this.parent.querySelectorAll("table"),t=[];for(var i in e)if(e.hasOwnProperty(i)){for(var o=(i=e[i]).rows,n=[],r=0;r<o.length;r++){var s=o[r];if(s.getElementsByTagName("select")[0].value){var l=Array.prototype.slice.call(s.cells[3].querySelectorAll("input, select")).map(function(e){return"checkbox"==e.type?e.checked?e.value:null:"button"!=e.type?e.value:void 0});l=l.filter(function(e){return null!==e});var a={column:s.cells[1].firstChild.value,operator:s.cells[2].firstChild.value,values:l};a.operator&&n.push(a)}}t.push({operator:i.parentNode.firstChild.firstChild.value,conditions:n})}return t}}(); |