例如,我想从数据库获取用户信息、电子邮件及其角色,并创建一个如下对象:
{
"id": 1,
"firstname": "John",
"lastname": "Johnny",
"emails": [
{
"type": "work",
"email": "[email protected] /cdn-cgi/l/email-protection"
},
{
"type": "personal",
"email": "[email protected] /cdn-cgi/l/email-protection"
}
],
"roles": [
{
"role": "ADM",
"title": "Admin"
},
{
"role": "PUB",
"title": "Publisher"
}
]
}
我需要查询三个表:
-
Users
表有id
, firstname
,lastname
.
-
Emails
表有type
, email
,user_id
.
-
Roles
表有role
, title
,user_id
.
基于pg-承诺 https://github.com/vitaly-t/pg-promise的维基我几乎确定它必须使用来完成Tasks https://github.com/vitaly-t/pg-promise/wiki/Learn-by-Example#tasks但不确定你会如何链接它们。
UPDATE在我的实际项目中,我必须插入一个产品并使用生成的 id 来插入属性。如果您遇到类似情况,请在此处添加我的代码:
//Insert a new product with attribites as key value pairs
post_product_with_attr: function(args) {
return db.task(function(t) {
return t.one(sql.post_new_product, args)
.then(function(dt) {
var queries = [];
Object.keys(args).forEach(function(key) {
queries.push(t.one(sql.post_attr_one, { Id: dt.id, key: key, value: args[key] }));
});
return queries.length ? t.batch(queries) : [dt];
});
});
}