'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 |
|---|
