使用node-postgres创建一个依赖于先前查询结果的查询

Question

对postgres数据库的第一个查询是SELECT查询，获取当前所有的shift/break数据，用于确定扫描的类型

第二个查询是一个INSERT查询，它依赖于第一个查询

的结果我的handler函数现在看起来是这样的:

const scanEvent = (request, response) => {
employee_id = request.body.employee_id;
var shifts;
Promise.all([
pool.query('Select * FROM shifts WHERE employee_id=$1 AND date=CURRENT_DATE', [employee_id])
]).then(function([queryResults]) {
shifts = queryResults.rows;
}).catch(function(e) {
response.status(500).send('Error retrieving data');
})
// based on the results of the query there will be a bunch of different cases for
// INSERT queries to put in the proper data to the database
if(shifts.length == 0) {
Promise.all([
pool.query('INSERT INTO shifts (employee_id, start_time) VALUES ($1, NOW())', [employee_id])
]).then(function() {
response.status(204).send('Successfully Inserted');
}).catch(function (e) {
response.status(500).send("Error");
});
} // else if ... handle all other cases
}

我的问题是我无法访问第一个查询的结果，因为似乎shifts变量在第一个Promise.all

的范围内是局部的

** EDIT **

我现在已经意识到我的方法不是最优的(只是学习node-postgres)一个更好的方法来解决这个问题是使用async/await:

const scanEvent = async (request, response) => {
employee_id = request.body.employee_id;
var shifts;
const getShifts = await pool.query('Select * FROM shifts WHERE employee_id=$1 AND date=CURRENT_DATE', [employee_id]);
shifts = getShifts.rows;
// based on the results of the query there will be a bunch of different cases for
// INSERT queries to put in the proper data to the database
if(shifts.length == 0) {
await pool.query('INSERT INTO shifts (employee_id, start_time) VALUES ($1, NOW())', [employee_id]);
} // else if ... handle all other cases
}

Answer 1

在执行if语句时，变量shifts将还没有值，因为它只在.then函数中接收它的值。因此，如果代码的后半部分依赖于shifts的值，请将其移到.then函数中:

.then(function([queryResults]) {
shifts = queryResults.rows;
if(/* first scan therefore scanning in for shift */) {
...
} // else if ... handle all other cases
})

(如果您希望并行执行两个独立的查询，请参阅此处)