How do I match a key with an object with sequelize?-CodePudding

Current Situation

My current Sequelize Controller looks like the below:

const getSum = async(req, res) => {
    const expenses = await Expense.findAll({
        attributes: [
            'datepick',
            'itemized_expense',
            [sequelize.fn('SUM', sequelize.col('expense_amount')), 'itemized_expense_sum']],
        group: ['datepick', "itemized_expense"]
      })
    

    res.status(200).json(expenses)

When I use postman, the cCurrent JSON response is below:

[
    {
        "datepick": "2022-10-03",
        "itemized_expense": "marketing",
        "itemized_expense_sum": "4000"
    },
    {
        "datepick": "2022-10-03",
        "itemized_expense": "development",
        "itemized_expense_sum": "8000"
    },
    {
        "datepick": "2022-10-03",
        "itemized_expense": "welfare",
        "itemized_expense_sum": "1111"
    },
    {
        "datepick": "2022-11-14",
        "itemized_expense": "wages",
        "itemized_expense_sum": "6000"
    },
    {
        "datepick": "2022-10-03",
        "itemized_expense": "wages",
        "itemized_expense_sum": "1111"
    },
    {
        "datepick": "2022-11-15",
        "itemized_expense": "development",
        "itemized_expense_sum": "1000"
    }
]

The Problem

How do I change my Sequelize Controller so that the response looks like the below?

{
  key: '10/20', /*this is datepick from above*/ 
  data: {
    wages: 500000, /*this is a key-value pair of itemized_expense & itemized_expense_sum from above*/ 
    welfare: 300000,
    marketing: 500000,
    development: 200000,
    rental: 150000
  }
},
{
  key: '10/21',
  data: {
    wages: 300000,
    welfare: 400000,
    marketing: 100000,
    development: 500000,
    rental: 250000
  }
},

I was trying to use Object.key() and map but I'm not sure if that is correct

CodePudding user response：

You can use lodash package to group and rearrange items:

const items = [
    {
        "datepick": "2022-10-03",
        "itemized_expense": "marketing",
        "itemized_expense_sum": "4000"
    },
    {
        "datepick": "2022-10-03",
        "itemized_expense": "development",
        "itemized_expense_sum": "8000"
    },
    {
        "datepick": "2022-10-03",
        "itemized_expense": "welfare",
        "itemized_expense_sum": "1111"
    },
    {
        "datepick": "2022-11-14",
        "itemized_expense": "wages",
        "itemized_expense_sum": "6000"
    },
    {
        "datepick": "2022-10-03",
        "itemized_expense": "wages",
        "itemized_expense_sum": "1111"
    },
    {
        "datepick": "2022-11-15",
        "itemized_expense": "development",
        "itemized_expense_sum": "1000"
    }
]
const groupedItems = _.groupBy(items, x => x.datepick)
const keys = Object.keys(groupedItems)
const mappedItems = keys.map(x => ({
  key: x,
  data: _.fromPairs(groupedItems[x].map(item => [item.itemized_expense, item.itemized_expense_sum])
  )
}))
result = mappedItems

result:

[
   {
      "key": "2022-10-03",
      "data": {
         "marketing": "4000",
         "development": "8000",
         "welfare": "1111",
         "wages": "1111"
      }
   },
   {
      "key": "2022-11-14",
      "data": {
         "wages": "6000"
      }
   },
   {
      "key": "2022-11-15",
      "data": {
         "development": "1000"
      }
   }
]

You can try this code in this lodash sandbox