使用pg-promise格式化JSON输出，将外键封装为对象

I am building a very simple REST platform using Node.JS and PostgreSQL, using pg-promise to access the database. My goal is to create an incredibly simple ticketing system with users and tickets. At this point I simply want to be able to query the server for a list of all tickets.

我正在使用Node构建一个非常简单的REST平台。JS和PostgreSQL，使用pg-promise访问数据库。我的目标是创建一个非常简单的票务系统与用户和票务。此时，我只是希望能够查询服务器的所有罚单列表。

My database consists of two tables in the following format:

我的数据库包括以下两张表格:

CREATE TABLE people (
  ID SERIAL PRIMARY KEY,
  NAME VARCHAR(128)
);

CREATE TABLE tickets (
  ID SERIAL PRIMARY KEY,
  SUBMITTED_BY int4 REFERENCES people(ID),
  TITLE VARCHAR(128)

As you can see there is a single foreign key pointing tickets to persons. I have populated my database with the following data:

正如您所看到的，这里有一个指向个人的外国钥匙。我用以下数据填充我的数据库:

coop=> SELECT * FROM people;
 id |   name   
----+----------
  1 | John Doe
(1 row)

coop=> SELECT * FROM tickets;
 id | submitted_by |      title       
----+--------------+------------------
  1 |            1 | My first ticket!
(1 row)

And I use pg-promise with the following query function to render a JSON response:

我使用以下查询函数pg-promise来呈现JSON响应:

// GET ALL query function
function getAllTickets(req, res, next) {
  db.any('select * from tickets left join people on tickets.submitted_by = people.id;')
    .then(function (data) {
      res.status(200)
        .json({
          status: 'success',
          data: data,
          message: 'Retrieved ALL tickets'
        });
    })
    .catch(function (err) {
      return next(err);
    });
}

The function works, I can retrieve JSON output in the following format:

函数工作，我可以以以下格式检索JSON输出:

:: GET /api/tickets
    {"status":"success","data":[{"id":1,"submitted_by":1,"title":"My first ticket!","name":"John Doe"}],"message":"Retrieved ALL tickets"}

However this is not the format I want. I want to encapsulate the 'persons' object within the 'tickets' object, replacing the foreign key as follows:

但这不是我想要的格式。我想将“person”对象封装在“tickets”对象中，替换为如下所示的外键:

{"status":"success","data":[{"id":1,submitted_by: {"id": 1, "name":"John Doe"},"title":"My first ticket!"}],"message":"Retrieved ALL tickets"}

The reason I want to do this so so I can easily consume this API from an Angular2 service and translate both ticket and person into object in my component. However I have absolutely no idea how to go about doing this and the pg-promise documentation has not been helpful.

我这样做的原因是，我可以很容易地从Angular2服务中使用这个API，并将ticket和person转换成我的组件中的对象。然而，我完全不知道该怎么做，而且pg-promise文档也没有帮助。

I have been web-programming with Django for the past year but since I switched to Node.JS I feel like a complete novice. Could somebody offer me a hand or point me in the right direction?

在过去的一年中，我一直在使用Django进行web编程，但是自从我切换到Node之后。我觉得自己是个完全的新手。有人能帮我一把，或者指点我正确的方向吗?

1 个解决方案

#1

pg-promise executes queries directly, and provides data exactly as it is sent by the server. It does not do any additional transformation, as it is not an ORM.

pg-promise直接执行查询，并提供与服务器发送的数据完全相同的数据。它不做任何额外的转换，因为它不是一个ORM。

If you want your data transformed the way you described, then just do it yourself, for it is trivial.

如果你想要你的数据改变你描述的方式，那么就自己做吧，因为它是微不足道的。

The only way pg-promise would be able to transform data in the way you described, if there were a separation on the query level, i.e. if there were a parent query + child query instead of a single join query. For this kind of example see Get a parents + children tree with pg-promise.

pg-promise的惟一方式是，如果查询级别上有分离，例如，如果有父查询+子查询而不是单个连接查询，那么可以按照您所描述的方式转换数据。对于这类例子，请参见使用pg-promise的Get a parents + children tree。

It is however not something I would advise here, because a single join query is much more efficient. You should simply transform the data on your end once you receive it.

然而，我在这里不建议这样做，因为单个连接查询的效率要高得多。您应该在接收到数据后对其进行转换。

There are, of course, some ORM-s out there that can do it for you, if you decide to go that way, which would be a very different approach, away from direct query execution.

当然，如果您决定采用这种方式，也可以使用一些ORM-s，这是一种非常不同的方法，而不是直接执行查询。

#1