sql-servertypescripttypeormnode-mssql

Convert nested array to nested object


I'm using typeorm to get data form DB and I want to list all my issues with the last status for each issue.

My database has 2 tables: Issues and IssueStatus Into IssueStatus I'll save every status changing and comments about how to solve the Issue.

My code:

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();

I'm getting this result:

{
    "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
      }
    ]
  }

But I want this:

{
    "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
  }

Solution

  • You could make smth like below, but you will need to overwrite all required fields with aliases in 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
    

    And what I mean by

    you will need to overwrite all required fields with aliases in 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'
    ])