mysql - 我需要帮助编写家谱的 MySQL INSERT 查询

Question

我是后端编程新手，需要帮助在我制作的 SQL 模式下为此编写插入查询。如有任何帮助，我们将不胜感激，提前致谢:我需要 POST 请求方面的帮助来存储具有关系的组织(父子关系)。组织名称是唯一的。所有关系和组织均通过一个请求插入

数据库架构

 CREATE TABLE `orgs` (
 `id` int(10) UNSIGNED NOT NULL,
 `org_name` varchar(250) NOT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=utf16;


 CREATE TABLE `orgs_relation` (
 `org_id` int(10) UNSIGNED NOT NULL,
 `parent_org_id` int(10) UNSIGNED NOT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=utf16;

ALTER TABLE `orgs`
 MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

插入数据应采用 JSON 格式。示例数据如下:

"org_name": "Paradise Island", 
"daughters": [{ 
"org_name": "Banana tree", 
"daughters": [{ 
"org_name": "Yellow Banana" 
}, { 
"org_name": "Brown Banana" 
}, { 
"org_name": "Black Banana" 
}] }, 

我已经成功地完成了 POST 请求，但需要帮助来继续查询以及如何执行递归:这是我到目前为止所做的；

var express = require("express");
var app = express();
var bodyParser = require("body-parser");
var expressSanitizer = require("express-sanitizer");
var mysql = require("mysql");


var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    database: 'be_task'
});

 app.use(bodyParser.json());
  app.use(expressSanitizer());
   
 app.get("/api/get/:name", function (req, res) {
    // find count of users in db
    var orgName = req.sanitize(req.params.name); // sanitizing orgName before using it in sql query
    console.log(orgName);
    var limit = 100; // setting limits of page to be returned with one request
    var offset = 0; // getting offset number
    var sQuery = `
        SELECT orgs.org_name as org_name, 'parent' as relationship_type FROM orgs_relation
        JOIN orgs ON orgs_relation.parent_org_id = orgs.id
        WHERE orgs_relation.org_id = (SELECT id FROM orgs WHERE org_name= ?)
        UNION
        SELECT orgs.org_name as org_name, 'daughter' as relationship_type FROM orgs_relation
        JOIN orgs ON orgs_relation.org_id = orgs.id
        WHERE orgs_relation.parent_org_id = (SELECT id FROM orgs WHERE org_name= ?)
        UNION
        SELECT orgs.org_name as org_name, 'sister' as relationship_type FROM orgs_relation AS or1
        JOIN orgs_relation AS or2 ON or1.parent_org_id = or2.parent_org_id
        JOIN orgs ON or2.org_id = orgs.id
        WHERE or1.org_id = (SELECT id FROM orgs WHERE org_name= ?) AND orgs.org_name <> ?
        ORDER BY org_name ASC
        LIMIT ? OFFSET ?
    `;
 
    connection.query(sQuery, [orgName, orgName, orgName, orgName, limit, offset], function (error, results, fields) {
        if (error) {
             return console.log(error);
        }
        res.setHeader('Content-Type', 'application/json');
        res.end(JSON.stringify(results));
    });
 
});

app.post("/api/create", function (req, res) {
    var body = '';
    req.on('data', function (data) {
        body += data;
        // If someone is trying to nuke RAM, nuke the request
        // 1e6 === 1 * Math.pow(10, 6) === 1 * 1000000 ~~~ 1MB
        if (body.length > 1e6) {
            req.connection.destroy();
        }
    });
    req.on('end', function () {
        process_request(body);
    });
    function process_request(body) {
        // Continue with parsing json string in body and inserting organisation
        // json string is stored in the body variable
        var org = JSON.parse(body);
        console.log(org);
        res.end(body);
    }
      var q =
         `  // "INSERT INTO orgs (id, org_name) VALUES 
             
    
              `;
     connection.query(q, body, function(err, result) {
        if(err){
          console.log(err);
        }
        console.log(result);
        res.redirect("/get/:name");
    });
});

Answer 1

由于您的代码不完整，我将假设 connection 是到您的 SQL DB 的连接并支持 Promise(例如 https://github.com/lukeb-uk/node-promise-mysql )

<小时/>

首先是递归函数(我将它们分成 2 个以便更容易理解)

// Explore `data` to extract the org_names
function getOrgNames(data) {
    if (Array.isArray(data)) {
        return data.map( (entry) => (getOrgNames(entry.daughters).concat([entry.org_name])) );
    } else if (data instanceof Object && data.org_name) {
        if (Array.isArray(data.daughters) && data.daughters.length != 0) {
            return getOrgNames(data.daughters).concat([data.org_name]);
        } else {
            return data.org_name;            
        }
    }
    // Else = Non array and invalid JSON entry => drop it
    return [];
}


// Transform an array of arrays into a single array of strings
function flatten(array) {
    return Array.isArray(array) ?
        array.reduce( (aggreg, entry) => (aggreg.concat(Array.isArray(entry) ? flatten(entry) : entry)), [] ) :
        array;
}

作为其作用的说明

var data = {
    "org_name": "Paradise Island",
    "daughters": [{
        "org_name": "Banana tree",
        "daughters": [{
            "org_name": "Yellow Banana"
        }, {
            "org_name": "Brown Banana"
        }, {
            "org_name": "Black Banana"
        }]
    }]
};

console.log("Data = %s\ngetOrgNames(data) = %s\nflatten(getOrgNames(data)) = %s", JSON.stringify(data, null, 4), JSON.stringify(getOrgNames(data), null, 4), JSON.stringify(flatten(getOrgNames(data)), null, 4));

输出以下内容:

Data = {
    "org_name": "Paradise Island",
    "daughters": [
        {
            "org_name": "Banana tree",
            "daughters": [
                {
                    "org_name": "Yellow Banana"
                },
                {
                    "org_name": "Brown Banana"
                },
                {
                    "org_name": "Black Banana"
                }
            ]
        }
    ]
}
getOrgNames(data) = [
    [
        [
            "Yellow Banana"
        ],
        [
            "Brown Banana"
        ],
        [
            "Black Banana"
        ],
        "Banana tree"
    ],
    "Paradise Island"
]
flatten(getOrgNames(data)) = [
    "Yellow Banana",
    "Brown Banana",
    "Black Banana",
    "Banana tree",
    "Paradise Island"
]

<小时/>

那么只需构建适当的 promise 链即可

var Promise = require('<your promise library>'); // such as 'bluebird' or 'when'

app.post("/api/create", function (req, res) {
    var body = '';
    req.on('data', function (data) {
        body += data;
        // If someone is trying to nuke RAM, nuke the request
        // 1e6 === 1 * Math.pow(10, 6) === 1 * 1000000 ~~~ 1MB
        if (body.length > 1e6) {
            req.connection.destroy();
        }
    });
    req.on('end', function () {
        process_request(body);
    });

    function getQueryFromOrgName(name) {
        // return your SQL query built from name
    }

    function process_request(body) {
        var data = JSON.parse(body);

        var orgToCreateList = flatten(getOrgNames(data));

        var promiseArray = orgToCreateList.map( (name) => (connection.query(getQueryFromOrgName(name))) );

        Promise.all(promiseArray)
        .then(function(results) {
            console.log(results);
            return res.redirect("/get/:name");
        }).catch(function(err) {
            console.log(err);
            return res.send("Error processing the query => "+err);
        })
    }
});

<小时/>

我建议您查看express它是一个非常强大的 Web 框架，将大大简化您对路由的处理