'How to transform a single table query output in typeorm

id code category_id currency_id quantity currency denomination_id denomination symbol
1 FNC-64119 1 1 4000 Naira 1 500
2 FNC-64118 1 1 1000 Naira 1 500
3 FNC-64117 1 1 10 Naira 2 100
4 FNC-64116 1 2 900 Dollar 3 5 $
5 FNC-64115 1 2 60 Dollar 4 10 $
6 FNC-64114 2 2 800 Dollar 5 100 $
7 FNC-64113 2 2 75 Dollar 6 1 $

I have the above table in PostgreSQL database table, I want to query the table and transform the output into the below JSON format using nestjs typescript

[
  {
    "currency_id": 1,
    "currency": "Naira",
    "symbol": "₦",
    "total": ? /*SUM(amount)*/,
    "denminations": [
      {
        "denomination_id": 1,
        "denomination": 500,
        "quantity": 4000,
        "amount": ? /*amount: (denomination * quantity)*/
      },
      {
        "denomination_id": 1,
        "denimination": 500,
        "quantity": 1000,
        "amount": ? /*amount: (denomination * quantity)*/
      }
    ]
  },
  {
    "currency_id": 2,
    "currency": "Dollar",
    "symbol": "$",
    "total": ? /*SUM(amount)*/,
    "denminations": [
      {
        "denomination_id": 3,
        "denomination": 5,
        "quantity": 900,
        "amount": ? /*amount: (denomination * quantity)*/
      },
      {
        "denomination_id": 4,
        "denomination": 10,
        "quantity": 60,
        "amount": 0
      }
    ]
  }
]

My code

 const qb = await getRepository(FaultyNotesRequestEntity)
  .createQueryBuilder("faulty_notes_request")
  .select("(faulty_notes_request.quantity*faulty_notes_request.denomination)", "amount")
  .addSelect("faulty_notes_request.denomination", "denomination")
  .addSelect("faulty_notes_request.denomination_id", "denomination_id")
  .addSelect('faulty_notes_request.quantity', 'quantity')
  .groupBy("faulty_notes_request.denomination,faulty_notes_request.denomination_id, faulty_notes_request.quantity")

const notes = await qb.getRawMany();

const qb2 = await getRepository(FaultyNotesRequestEntity)
.createQueryBuilder('faulty_notes_request')
.select('faulty_notes_request.currency_id', 'currency_id')
.addSelect('faulty_notes_request.symbol', 'symbol')
.addSelect(`SUM(${notes.map(x => x.amount)})`, 'total')
.addSelect('faulty_notes_request.currency', 'currency')
.groupBy("faulty_notes_request.currency_id,faulty_notes_request.symbol, faulty_notes_request.currency, faulty_notes_request.id")

const notes2 = await qb2.getMany();

let resp = notes2.map(x => {
  pool.currency_id = x.currency_id,
  pool.currency = x.currency,
  pool.symbol = x.symbol,
  pool.total = x./*total - does not show-up here*/
})

This is where I get stuck. Any help will be appreciated



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source