时间戳

首页 > 解决方案 > 将嵌套数组转换为嵌套对象

sql-server - 将嵌套数组转换为嵌套对象

问题描述

我正在使用 typeorm 从 DB 获取数据，我想列出我的所有问题以及每个问题的最后状态。

我的数据库有 2 个表：Issue 和 IssueStatus 到 IssueStatus 我将保存每个状态更改和有关如何解决问题的评论。

我的代码：

var issues = await issuesRepository
    .createQueryBuilder('issue')
    .select(['issue', 'history.status'])
    .innerJoin('issue.history', 'history')
    .leftJoin(
      'issue.history',
      'history2',
      'history.date < history2.date OR (history.date = history2.date AND history.id < history2.id)'
    )
    .where('history2.id IS NULL')
    .getMany();

我得到这个结果：

{
    "id": "5ff86c81-a202-4211-84f4-afe2d5c0fc0d",
    "cod": 1,
    "opendate": "2020-12-08T13:18:55.683Z",
    "closedate": null,
    "type": "systems",
    "subtype": "avance",
    "description": "first test",
    "mailto": "sustentacao@bonnjur.com.br",
    "hostname": "dskcwbti02",
    "username": "glenn.veloso",
    "solution": null,
    "supportby": null,
    "history": [
      {
        "status": 0
      }
    ]
  }

但我想要这个：

{
    "id": "5ff86c81-a202-4211-84f4-afe2d5c0fc0d",
    "cod": 1,
    "opendate": "2020-12-08T13:18:55.683Z",
    "closedate": null,
    "type": "systems",
    "subtype": "avance",
    "description": "first test",
    "mailto": "sustentacao@bonnjur.com.br",
    "hostname": "dskcwbti02",
    "username": "glenn.veloso",
    "solution": null,
    "supportby": null,
    "status": 0
  }

标签： sql-servertypescripttypeormnode-mssql

解决方案

你可以像下面那样做 smth，但你需要用别名覆盖所有必填字段select：

var issues = await issuesRepository
    .createQueryBuilder('issue')
    .select(['issue', 'history.status AS status']) // <- changes here
    .innerJoin('issue.history', 'history')
    .leftJoin(
      'issue.history',
      'history2',
      'history.date < history2.date OR (history.date = history2.date AND history.id < history2.id)'
    )
    .where('history2.id IS NULL')
    .getRawMany(); // <- changes here

我的意思是

您将需要用别名覆盖所有必填字段select

.select([
  'issue.id AS id',
  'issue.cod AS cod',
  'issue.opendate AS opendate',
...
...
...
  'issue.solution AS solution',
  'issue.supportby AS supportby',
  'history.status AS status'
])

推荐阅读