使用pg-promise查询多对多关系的最佳方法 [英] Best way to query a Many to Many Relationship using pg-promise

问题描述

例如，我想从数据库中获取用户信息，电子邮件及其角色，并创建一个像这样的对象：

For example I want to get the user info, emails and it's roles from db and create an object like :

{
  "id": 1,
  "firstname": "John",
  "lastname": "Johnny",
  "emails": [
    {
      "type": "work",
      "email": "[e-mail]"
    },
    {
      "type": "personal",
      "email": "[e-mail]"
    }
  ],
  "roles": [
    {
      "role": "ADM",
      "title": "Admin"
    },
    {
      "role": "PUB",
      "title": "Publisher"
    }
  ]
}

我需要查询三个表：

• Users 表的 id ，名字，姓氏。


• 电子邮件表具有类型，电子邮件， user_id 。


• Roles 表具有 role ，标题， user_id 。

• Users table has id, firstname,lastname.
• Emails table has type, email,user_id.
• Roles table has role, title,user_id.

基于 pg-promise 的Wiki，我几乎可以肯定必须使用任务，但不确定如何链接它们。

Based on the pg-promise's wiki I am almost sure it has to be done using Tasks but not sure how would you chain them.

更新在我的实际项目中，我必须插入产品并使用生成的ID插入属性。如果您遇到类似的情况，请在此处添加我的代码：

UPDATE In my actual project I had to insert a product and use the generated id to insert attributes. Adding my code here in case you have a similar situation:

//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];
            });
    });
}

推荐答案

您所描述的是' t多对多关系是2个一对多关系。

What you are describing isn't a Many-to-Many relationship, it is 2 One-To-Many relationships.

基于 pg-promise 的Wiki我几乎可以肯定必须使用任务，但不确定如何链接它们。

Based on the pg-promise's wiki I am almost sure it has to be done using Tasks but not sure how would you chain them.

在您的示例中，无需链接任何东西。一条链承诺何时需要将一个结果作为下一个条件，因为这是保证的工作方式，而不仅仅是查询。

In your example there is no need to chain anything. One chains promises when the result of one needs to be used as the criteria for the next one, because this is how promises work, not just queries.

因此，您可以并行或独立执行所有3个查询，如下例所示：

Because of that, you can execute all 3 queries either in parallel, or independently, as shown in the example below:

function getUserInfo(userId) {
    return db.task(async t => {
        const user = await t.one('SELECT id, firstname, lastname FROM Users WHERE id = $1', userId);
        const emails = await t.any('SELECT type, email FROM emails WHERE user_id = $1', userId),
        const roles = await t.any('SELECT role, title FROM roles WHERE user_id = $1', userId);

        user.emails = emails;
        user.roles = roles;

        return user;
});

用法示例：

const user = await getUserInfo(123); //=> user object with all details

请注意，通过现有的PostgreSQL JSON函数执行此操作效率更高，这样您就可以只执行一个查询。

Note that it is much more efficient to do this via existing PostgreSQL JSON functions, which let you execute just one query then.

这篇关于使用pg-promise查询多对多关系的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！