我目前正在开发Node的小型网络服务,以管理餐厅中的命令。我正在使用express.js
在我的数据库中,我得到了该表:
DISH(id,name)
COMMAND(id,table,status)
COMMAND_DISH(idCommand,idDish,quantity)
我想知道是否有一种方法可以为每个命令构建这样的JSON对象:
[
{
"idCommand": 1,
"table": 21,
"dishes": {
"dish1": {
"dishName1": "salad",
"quantity": 2
},
"dish2": {
"dishName1": "steak",
"quantity": 2
}
}
}
]
知道我的查询像这样返回行
命令:
id table status
1 3 sent
2 4 sent
3 5 sent
command_dish:
idCommand idDish quantity
1 2 2
1 3 2
2 2 4
2 1 1
2 3 5
菜:
id name
1 salad
2 steak
3 pasta
4 pizza
我在这里找不到的是如何构建JSON对象,当我从command_dish
获得多个命令时编辑:到目前为止,这就是我尝试构建数据的方式,但我不确定查询中的查询是否可行?
app.get("/commandes", function(req, res, next) {
var restaurant = req.query.restaurant;
connection.connect();
var tableauCommande= new Object();
connection.query('SELECT * FROM COMMANDE WHERE restaurant=?',restaurant,function(err, rows, fields) {
if (err) throw err;
for (var i = 0; i < rows.length; i++) {
var tableauResult = new Object();
tableauResult["idCommande"]=rows[i].id;
tableauResult["table"]=rows[i].taable;
var tableauPlats = new Object();
connection.query('SELECT P.label, C.quantite FROM PLAT AS P, COMMANDE_PLAT AS C WHERE C.plat=P.id AND C.commande=?',rows[i].id,function(err, rows, fields) {
if (err) throw err;
for (var j = 0; j < rows.length; j++) {
tableauPlats[rows[j].label]=rows[j].quantite;
}
tableauResult["plats"]=JSON.stringify(tableauPlats);
JSON.stringify(tableauResult);
});
tableauCommande[rows[i].id]=JSON.stringify(tableauResult);
}
});
});
edit2:我更改了代码:
app.get("/commandes", function(req, res, next) {
var restaurant = req.query.restaurant;
var tableauResult= new Object();
connection.connect();
connection.query('SELECT * FROM COMMANDE WHERE restaurant=?',restaurant,function(err, rows, fields) {
if (err) throw err;
for (var i = 0; i < rows.length; i++) {
var tableauCommande = new Object();
tableauCommande["table"]=rows[i].taable;
tableauResult[rows[i].id]=tableauCommande;
}
});
connection.end();
console.log(tableauResult);
for (var key in tableauResult) {
var tableauPlats = new Object();
connection.connect();
connection.query('SELECT P.label, C.quantite FROM PLAT AS P, COMMANDE_PLAT AS C WHERE C.plat=P.id AND C.commande=?',key,function(err, rows, fields) {
if (err) throw err;
for (var j = 0; j < rows.length; j++) {
tableauPlats[rows[j].label]=rows[j].quantite;
}
tableauResult[key]["plats"]=tableauPlats;
});
connection.end();
}
console.log(tableauResult);
});
似乎更稳定,但是我得到了"调用退出后无法征询查询"错误。我不明白,因为我正确管理连接
如果有人有兴趣,我设法构建了这样的json对象
app.get("/commandes", function(req, res, next) {
var restaurant = req.query.restaurant;
var tableauResult= new Object();
connection.connect();
connection.query('SELECT CP.commande, C.taable, C.status, P.label, CP.quantite FROM PLAT AS P, COMMANDE AS C, COMMANDE_PLAT AS CP WHERE CP.plat=P.id AND C.id=CP.commande AND C.restaurant=?',restaurant,function(err, rows, fields) {
if (err) throw err;
for (var i = 0; i < rows.length; i++) {
if(rows[i].commande in tableauResult){
tableauResult[rows[i].commande]["plats"][rows[i].label]=rows[i].quantite;
}else{
var tableauCommande = new Object();
tableauCommande["table"]=rows[i].taable;
tableauCommande["status"]=rows[i].status;
var tableauPlats = new Object();
tableauPlats[rows[i].label]=rows[i].quantite;
tableauCommande["plats"]=tableauPlats;
tableauResult[rows[i].commande]=tableauCommande;
}
}
res.json(tableauResult);
});
connection.end();
});
棘手的是,即使您正确打开并关闭连接,MySQL节点包似乎也不喜欢两个查询,因此我将所有内容加载到一个查询中,我必须自己对结果进行分类。