這是動態數組數據。我在下面提到了我需要生成SQL查詢的示例數據我正在使用 VueJs + Laravel。下面我更新了數組數據和方法[ { "operator": "AND", "rules": [ { "id": 100, "column": "dd_Tttp", "type": "equal", "value": true, "join": "AND", } ], "groups": [ { "operator": "AND", "rules": [ { "id": 200, "column": "dd_tering", "type": "equal", "value": true, "join": "AND", }, { "id": 201, "column": "dd_Size", "type": "in", "value": "Standard", "join": "AND", }, { "id": 202, "column": "dd_Lotpth", "type": "equal", "value": "12", "join": "AND", } ], "groups": [ { "operator": "AND", "rules": [ { "id": 300, "column": "dd_cat", "type": "equal", "value": "34", "join": "AND", }, { "id": 301, "column": "dd_Cot", "type": "in", "value": "Coftlassic", "join": "AND", }, { "id": 302, "column": "dd_dse", "type": "equal", "value": "2020-01-01", "join": "AND", }, { "id": 303, "column": "dd_turflaid", "type": "equal", "value": true, "join": "AND", } ], "groups": [ ] } ] } ] },將上面的數組數據傳遞給這個函數我想像這樣輸出
1 回答
慕標琳琳
TA貢獻1830條經驗 獲得超9個贊
我會使用不同的結構，因為：
沒有必要區分
operator和join。groupsrules應該是同一個概念。當它應該有嵌套規則時，只需使用嵌套rules屬性。當您使用類型
inorbetween時，該value屬性實際上應該是一個數組。解析器應該注入逗號和其他 SQL 語法。不要將逗號放在單個字符串中（盡管您沒有這樣的示例）避免解析
not_between和not_is_null分開；between它們與and是一樣的is_null，但NOT應用了 a 。這可以更動態地完成。
這是您的示例的建議結構：
{
"type": "AND",
"rules": [{
"id": 100,
"column": "dd_Tttp",
"type": "equal",
"value": true
}, {
"type": "AND",
"rules": [{
"id": 200,
"column": "dd_tering",
"type": "equal",
"value": true
}, {
"id": 201,
"column": "dd_Size",
"type": "in",
"value": ["Standard"] // You should use arrays for type="in"
}, {
"id": 202,
"column": "dd_Lotpth",
"type": "equal",
"value": "12"
}, {
"type": "AND",
"rules": [{
"id": 300,
"column": "dd_cat",
"type": "equal",
"value": "34"
}, {
"id": 301,
"column": "dd_Cot",
"type": "in",
"value": ["Coftlassic"] // You should use arrays for type="in"
}, {
"id": 302,
"column": "dd_dse",
"type": "equal",
"value": "2020-01-01"
}, {
"id": 303,
"column": "dd_turflaid",
"type": "equal",
"value": true
}]
}]
}, {
"type": "AND",
"rules": [{
"id": 100,
"column": "dd_get",
"type": "equal",
"value": true
}, {
"id": 101,
"column": "dd_ccc",
"type": "in",
"value": ["Standard"] // // You should use arrays for type="in"
}]
}]
};
這是一個從中生成 SQL 的片段：
const op = { equal: " = ", not_equal: " <> ", less: " < ", less_or_equal: " <= ",
greater: " > ", greater_or_equal: " >= " };
function toSql(rule) {
// recursive case:
if (rule.rules) return "(" + rule.rules.map(toSql).join("\n" + rule.type + " ") + ")";
// Base case (it is an atomic rule):
if (op[rule.type]) return rule.column + op[rule.type] + JSON.stringify(rule.value);
// Deal with "not": that is just a negation of the opposite
let type = rule.type.replace(/^not_/, "");
let sql = rule.column + (
type === "in" ? " IN (" + JSON.stringify(rule.value).slice(1,-1) + ")"
: type === "between" ? " BETWEEN " + rule.value.map(item => JSON.stringify(item)).join(" AND ")
: type === "is_null" ? " IS NULL"
: "<UNKNOWN TYPE:" + type + ">"
);
return type === rule.type ? sql : "NOT (" + sql + ")";
}
let rule = {"type": "AND","rules": [{"id": 100,"column": "dd_Tttp","type": "equal","value": true}, {"type": "AND","rules": [{"id": 200,"column": "dd_tering","type": "equal","value": true}, {"id": 201,"column": "dd_Size","type": "in","value": ["Standard"]}, {"id": 202,"column": "dd_Lotpth","type": "equal","value": "12"}, {"type": "AND","rules": [{"id": 300,"column": "dd_cat","type": "equal","value": "34"}, {"id": 301,"column": "dd_Cot","type": "in","value": ["Coftlassic"]}, {"id": 302,"column": "dd_dse","type": "equal","value": "2020-01-01"}, {"id": 303,"column": "dd_turflaid","type": "equal","value": true}]}]}, {"type": "AND","rules": [{"id": 100,"column": "dd_get","type": "equal","value": true}, {"id": 101,"column": "dd_ccc","type": "in","value": ["Standard"]}]}]};
console.log(toSql(rule));
禁用規則
在評論中，您添加了一個您希望禁用某些規則的要求。在這種情況下，首先按新屬性過濾規則disabled。
這是與該更改相同的代碼段，其中最后兩個（嵌套）規則被禁用：
const op = { equal: " = ", not_equal: " <> ", less: " < ", less_or_equal: " <= ",
greater: " > ", greater_or_equal: " >= " };
function toSql(rule) {
// recursive case:
if (rule.rules) {
// Filter out recursive return values that are empty (using Boolean):
let sql = rule.rules.map(toSql).filter(Boolean).join("\n" + rule.type + " ");
// return that SQL in parentheses, except when it is empty
return sql ? "(" + sql + ")" : "";
}
// Base case (it is an atomic rule):
if (rule.disabled) return ""; // Return empty string when disabled
if (op[rule.type]) return rule.column + op[rule.type] + JSON.stringify(rule.value);
// Deal with "not": that is just a negation of the opposite
let type = rule.type.replace(/^not_/, "");
let sql = rule.column + (
type === "in" ? " IN (" + JSON.stringify(rule.value).slice(1,-1) + ")"
: type === "between" ? " BETWEEN " + rule.value.map(item => JSON.stringify(item)).join(" AND ")
: type === "is_null" ? " IS NULL"
: "<UNKNOWN TYPE:" + type + ">"
);
return type === rule.type ? sql : "NOT (" + sql + ")";
}
let rule = {"type": "AND","rules": [{"id": 100,"column": "dd_Tttp","type": "equal","value": true}, {"type": "AND","rules": [{"id": 200,"column": "dd_tering","type": "equal","value": true}, {"id": 201,"column": "dd_Size","type": "in","value": ["Standard"]}, {"id": 202,"column": "dd_Lotpth","type": "equal","value": "12"}, {"type": "AND","rules": [{"id": 300,"column": "dd_cat","type": "equal","value": "34"}, {"id": 301,"column": "dd_Cot","type": "in","value": ["Coftlassic"]}, {"id": 302,"column": "dd_dse","type": "equal","value": "2020-01-01"}, {"id": 303,"column": "dd_turflaid","type": "equal","value": true}]}]}, {"type": "AND","rules": [{"id": 100,disabled:true,"column": "dd_get","type": "equal","value": true}, {"id": 101,disabled:true,"column": "dd_ccc","type": "in","value": ["Standard"]}]}]};
console.log(toSql(rule));
- 1 回答
- 0 關注
- 102 瀏覽
添加回答
舉報
0/150
提交
取消