将查询结果推送到对象中
我正在开展一个私人项目来创建我参加过的音乐会名单。
我有这些表:
- vi_concert(id,title,date,location)
- vi_artist(id,name,perma)
- vi_location(id,name,perma)
- vi_artist_concert(artist_id,concert_id)
我做了2个查询。一个用于音乐会信息,第二个用于排队信息:
router.get('/:id', (req, res) => {
var output = [];
// Get Concert Information
let concert = `SELECT c.date, c.title, l.name AS location
FROM vi_concert c
INNER JOIN vi_location l ON c.location=l.id
WHERE c.id = '${req.params.id}'`;
db.query( concert, (err, result) => {
if ( err ) throw err;
output.push(result);
});
let lineup = `SELECT a.name, a.perma
FROM vi_artist_concert ac
JOIN vi_artist a ON a.id = ac.artist_id
JOIN vi_concert c ON c.id = ac.concert_id
WHERE ac.concert_id = '${req.params.id}'`;
db.query( lineup, (err, result) => {
if ( err ) throw err;
output.push(result);
});
res.send(JSON.stringify(output));
});
当我调用URL localhost:3000 / concert / 1时,我得到以下内容:
[ ]
但是我想要这样的东西:
[
"concert": {
"date": "2019-02-16 19:30:00",
"title": "Fancy title",
"location": "Fancy location"
},
"lineup": [
{
"name": "Band 1",
"perma": "band-1"
},
{
"name": "Band 234",
"perma": "band-234"
}
]
]
回答如下:
Javascript是异步的。这意味着您的代码在不等待数据库查询结束的情况下发送JSON.stringify(output)
。
你必须要么:
- 首先查询,然后执行第二次查询,然后发送输出或
- 并行执行第一个和第二个查询,然后发送输出
没有任何额外库的第一种方式:
router.get('/:id', (req, res) => {
var output = [];
// Get Concert Information
let concert = `SELECT c.date, c.title, l.name AS location
FROM vi_concert c
INNER JOIN vi_location l ON c.location=l.id
WHERE c.id = '${req.params.id}'`;
let lineup = `SELECT a.name, a.perma
FROM vi_artist_concert ac
JOIN vi_artist a ON a.id = ac.artist_id
JOIN vi_concert c ON c.id = ac.concert_id
WHERE ac.concert_id = '${req.params.id}'`;
db.query( concert, (err, result) => {
if ( err ) throw err;
output.push(result);
db.query( lineup, (err2, result2) => {
if ( err2 ) throw err2;
output.push(result2);
res.send(JSON.stringify(output));
});
});
});
使用async
库的第二种方式:
var async = require('async');
router.get('/:id', (req, res) => {
var output = [];
// Get Concert Information
let concert = `SELECT c.date, c.title, l.name AS location
FROM vi_concert c
INNER JOIN vi_location l ON c.location=l.id
WHERE c.id = '${req.params.id}'`;
let lineup = `SELECT a.name, a.perma
FROM vi_artist_concert ac
JOIN vi_artist a ON a.id = ac.artist_id
JOIN vi_concert c ON c.id = ac.concert_id
WHERE ac.concert_id = '${req.params.id}'`;
async.parallel([
function(callback){
db.query( concert, (err, result) => {
if ( err ) return callback(err);
output.push(result);
});
}, function(callback){
db.query( lineup, (err, result) => {
if ( err ) return callback(err);
output.push(result);
});
}
], function(err){
if(err)
throw err;
res.send(JSON.stringify(output));
})
});
如果您的数据库驱动程序支持它们,您也可以使用promises,但我会在您自己的时间让您使用Google。
将查询结果推送到对象中
我正在开展一个私人项目来创建我参加过的音乐会名单。
我有这些表:
- vi_concert(id,title,date,location)
- vi_artist(id,name,perma)
- vi_location(id,name,perma)
- vi_artist_concert(artist_id,concert_id)
我做了2个查询。一个用于音乐会信息,第二个用于排队信息:
router.get('/:id', (req, res) => {
var output = [];
// Get Concert Information
let concert = `SELECT c.date, c.title, l.name AS location
FROM vi_concert c
INNER JOIN vi_location l ON c.location=l.id
WHERE c.id = '${req.params.id}'`;
db.query( concert, (err, result) => {
if ( err ) throw err;
output.push(result);
});
let lineup = `SELECT a.name, a.perma
FROM vi_artist_concert ac
JOIN vi_artist a ON a.id = ac.artist_id
JOIN vi_concert c ON c.id = ac.concert_id
WHERE ac.concert_id = '${req.params.id}'`;
db.query( lineup, (err, result) => {
if ( err ) throw err;
output.push(result);
});
res.send(JSON.stringify(output));
});
当我调用URL localhost:3000 / concert / 1时,我得到以下内容:
[ ]
但是我想要这样的东西:
[
"concert": {
"date": "2019-02-16 19:30:00",
"title": "Fancy title",
"location": "Fancy location"
},
"lineup": [
{
"name": "Band 1",
"perma": "band-1"
},
{
"name": "Band 234",
"perma": "band-234"
}
]
]
回答如下:
Javascript是异步的。这意味着您的代码在不等待数据库查询结束的情况下发送JSON.stringify(output)
。
你必须要么:
- 首先查询,然后执行第二次查询,然后发送输出或
- 并行执行第一个和第二个查询,然后发送输出
没有任何额外库的第一种方式:
router.get('/:id', (req, res) => {
var output = [];
// Get Concert Information
let concert = `SELECT c.date, c.title, l.name AS location
FROM vi_concert c
INNER JOIN vi_location l ON c.location=l.id
WHERE c.id = '${req.params.id}'`;
let lineup = `SELECT a.name, a.perma
FROM vi_artist_concert ac
JOIN vi_artist a ON a.id = ac.artist_id
JOIN vi_concert c ON c.id = ac.concert_id
WHERE ac.concert_id = '${req.params.id}'`;
db.query( concert, (err, result) => {
if ( err ) throw err;
output.push(result);
db.query( lineup, (err2, result2) => {
if ( err2 ) throw err2;
output.push(result2);
res.send(JSON.stringify(output));
});
});
});
使用async
库的第二种方式:
var async = require('async');
router.get('/:id', (req, res) => {
var output = [];
// Get Concert Information
let concert = `SELECT c.date, c.title, l.name AS location
FROM vi_concert c
INNER JOIN vi_location l ON c.location=l.id
WHERE c.id = '${req.params.id}'`;
let lineup = `SELECT a.name, a.perma
FROM vi_artist_concert ac
JOIN vi_artist a ON a.id = ac.artist_id
JOIN vi_concert c ON c.id = ac.concert_id
WHERE ac.concert_id = '${req.params.id}'`;
async.parallel([
function(callback){
db.query( concert, (err, result) => {
if ( err ) return callback(err);
output.push(result);
});
}, function(callback){
db.query( lineup, (err, result) => {
if ( err ) return callback(err);
output.push(result);
});
}
], function(err){
if(err)
throw err;
res.send(JSON.stringify(output));
})
});
如果您的数据库驱动程序支持它们,您也可以使用promises,但我会在您自己的时间让您使用Google。