mysql - 如何使用express在nodejs中进行mysql "ladder"查询

Question

我遇到了一个问题，当我想用​​nodejs、express和mysql以“阶梯”形式进行查询并返回一个json时，它对我不起作用，我想要的结构是:

select dev_id*,dev_name from devices
select cont_id*, cont_name from controllers where dev_id = dev_id
select act_id, acti_id from actions where cont_id = cont_id**

return json({devices,{controller,{actions}}})

我正在使用 forEach 函数，并且我还尝试使用回调函数。

Answer 1

您需要连接所有 3 个表，然后循环结果并构建嵌套对象。

var mysql = require("mysql");
var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});
var sql = `SELECT d.dev_id, d.dev_name, c.cont_id, c.cont_name, a.act_id, a.acti_id
           FROM devices AS d
           LEFT JOIN controllers AS c ON c.dev_id = c.dev_id
           LEFT JOIN actions AS a ON a.cont_id = c.cont_id`;
con.connect(err => {
    if (err) {
        throw err;
    }
    conn.query(sql, (err, result, fields) => {
        if (err) {
            throw err;
        }
        var devices = {};
        $result.forEach(({dev_id, dev_name, cont_id, cont_name, act_id, acti_id}) => {
            devices[dev_id] = devices[dev_id] || {id: dev_id, name: dev_name, controllers: {}};
            devices[dev_id].controllers[cont_id] = devices[dev_id].controllers[cont_id] || {id: cont_id, name: cont_name, actions: {}};
            devices[dev_id].controllers[cont_id].actions[act_id] = {id: act_id, acti_id: acti_id};
        });
    });
    // do something with devices here
});

请记住，所有mysql查询代码都是异步的，因此无法正常返回值。您需要使用回调或 promise 。

devices 变量如下所示:

{
    devid1: {
        id: "devid1",
        name: "devname1",
        controllers: {
            contid1: {
                id: "contid1",
                name: "contname1",
                actions: {
                    actionid1: {
                        id: "actionid1",
                        acti_id: "acti_id1"
                    },
                    actionid2: {
                        id: "actionid2",
                        acti_id: "acti_id2"
                    }
                }
            },
            contid2: {
                id: "contid2",
                name: "contname2",
                actions: {
                    actionid3: {
                        id: "actionid3",
                        acti_id: "acti_id3"
                    }
                }
            }
        |
    }
}