knex:从结果创建数组的合适方法是什么? [英] knex: what is the appropriate way to create an array from results?

问题描述

我有一个端点，该端点以一对多关系连接了user和user_emails表(postgresql).看起来如下.

I have an endpoint that joins the user and user_emails table as a one-to-many relationship (postgresql). It look as follows.

router.get('/', function (req, res, next) {
  db.select('users.id', 'users.name', 'user_emails.address')
    .from('users')
    .leftJoin('user_emails', 'users.id', 'user_emails.user_id')
    .then(users => res.status(200).json(users))
    .catch(next)  // go to error handler
});

但是，这将为每个电子邮件地址返回一个新文档.我想要的是一系列文档，如下所示:

However, this will return a new document for each email address. What I want is an array of documents that looks as follows:

[{
  id: 1,
  name: 'Steve',
  emails: [
    { address: '[e-mail]' },
    { address: '[e-mail]' }
  ]
}, {
  id: 2,
  name: 'Jimmy',
  emails: [
    { address: '[e-mail]' }
  ]
}]

该如何在knex中完成?

How should this be done in knex?

推荐答案

假设您正在使用Postgres-您需要使用array_agg函数来生成数组.我建议使用knex.raw

Assuming you're using Postgres - you need to use array_agg function to generate arrays. I would suggest using knex.raw

请告诉我这是否可行.

   knex('users')
    .innerJoin('user_emails','users.id','user_emails.user_id')
    .select([
      'users.id as userID',
      'users.name as userName',
      knex.raw('ARRAY_AGG(user_emails.adress) as email')
    ])
    .groupBy('users.id','users.name')

这篇关于knex:从结果创建数组的合适方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！