如何使用父子值从多维数组值中创建字符串？- 动态 SQL 查询生成器
这是动态数组数据。我在下面提到了我需要生成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": [
]
}
]
}
]
},
将上面的数组数据传递给这个函数我想像这样输出
人到中年有点甜浏览 152回答 1
1回答
-
慕标琳琳
我会使用不同的结构，因为：没有必要区分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));
随时随地看视频慕课网APP
PHP