目前我有以下代码来获取两个查询的结果
dbro.many("SELECT geoname_id,country_name FROM paises WHERE locale_code=$1 LIMIT 10",data.lang)
.then(function(countriesData){
data.countries=countriesData;
dbro.many("SELECT * FROM categorias")
.then(function(categoriesData){
data.categories=(categoriesData)
console.log(data);
res.render('layout',data);
res.end();
})
.catch(function(err){
console.log("error while fetching categories data");
})
})
.catch(function(err){
console.log("error while fetching countries data",err);
});
不知怎的,我认为这是不对的.如果我需要在返回回调之前获得许多查询的结果,该怎么办？几个then / catch的嵌套变得丑陋.目标是在呈现页面之前准备好所有数据(在Express中)
解决方法
pg-promise文档有
plenty of examples如何执行多个查询.
初始化
const pgp = require('pg-promise')(/* initialization options */);
const db = pgp('postgres://username:password@host:port/database');
db.task('get-user-events',t => {
return t.one('select * from users where id = $1',123)
.then(user => {
return t.any('select * from events where login = $1',user.name);
});
})
.then(data => {
// data = result from the last query;
})
.catch(error => {
// error
});
当查询没有依赖关系时,我们应该在任务中将它们作为batch执行：
db.task('get-everything',t => {
return t.batch([
t.any('select * from users'),t.one('select count(*) from events')
]);
})
.then(data => {
// data[0] = result from the first query;
// data[1] = result from the second query;
})
.catch(error => {
// error
});
请注意,我强调每个语句都带有“should”,因为您可以执行任务或事务之外的所有内容,但由于管理数据库连接的方式,因此不建议这样做.
当您需要为每个HTTP请求执行单个查询时,您应该只对根协议(db对象)执行查询.应始终在任务/事务中执行多个查询.
另见Chaining Queries,其主要点位于底部：
If you do not follow the advised approach,your application will perform better under a small load,due to more connections allocated in parallel,but under a heavy load it will quickly deplete the connection pool,crippling performance and scalability of your application.
UPDATE
从pg-promise v7.0.0开始,我们可以在一个命令中从多个查询中提取结果,这比以前的所有解决方案都高效得多：
db.multi('SELECT * FROM users;SELECT count(*) FROM events')
.then(data => {
// data[0] = result from the first query;
// data[1] = result from the second query;
})
.catch(error => {
// error
});
或者,如果您使用的是Bluebird,那么：
db.multi('SELECT * FROM users;SELECT count(*) FROM events')
.spread((users,[{count}]) => {
// users = array of user records;
// count = string with the count;
})
.catch(error => {
// error
});
请参阅新方法multi和multiResult.
