如何使用父子值从多维数组值中创建字符串？- 动态 SQL 查询生成器

Question

这是动态数组数据。我在下面提到了我需要生成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": [            ]          }        ]      }    ]  },将上面的数组数据传递给这个函数我想像这样输出

Answer 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));