I'm looking to adjust the data structure retrieved from an ORM query involving four tables. The product and category tables have a many-to-many relationship, with the product_category table serving as a bridge. Additionally, there's a fourth table called department. Here is how the associations are set up:

// product
product.belongsToMany(models.category, {
      through: 'product_category',
      foreignKey: 'product_id'
});

// product_category
product_category.belongsTo(models.product, {
      foreignKey: 'product_id'
});
product_category.belongsTo(models.category, {
      foreignKey: 'category_id'
});

// category
category.belongsToMany(models.product, {
      through: 'product_category',
      foreignKey: 'category_id'
});
category.belongsTo(models.department, {
      foreignKey: 'department_id'
});

// department
department.hasMany(models.category, {
      foreignKey: 'department_id'
});

Using this table structure, an ORM query like the following can be used to retrieve products corresponding to a specific department_id:

const query = await product.findOne({
   where: { product_id: id },
   include: {
     model: category,
     attributes: ['category_id', ['name', 'category_name']],
     include: {
       model: department,
       attributes: ['department_id', ['name', 'department_name']]
     }
   },
   attributes: []
});

const data = query.categories;

The resulting JSON data looks like this:

"data": [
    {
        "category_id": 1,
        "category_name": "French",
        "department": {
            "department_id": 1,
            "department_name": "Regional"
        },
        "product_category": {
            "product_id": 1,
            "category_id": 1
        }
    }
]

My goal is to transform the above data to look like this:

"data": [
    {
         "category_id": 1,
         "category_name": "French",
         "department_id": 1,
         "department_name": "Regional"
    }
]

In order to achieve this transformation, I've explored two methods - modifying the SQL-based ORM query or manipulating the product value in JavaScript. While I wasn't familiar with the first method due to my SQL learning background, I attempted the second method.

---

First Attempt

I made two attempts, taking into account that the framework utilizes Koa.js. The first attempt looked like this:

const query = await product.findOne({
  where: { product_id: id },
  include: {
    model: category,
    attributes: ['category_id', ['name', 'category_name']],
    include: {
      model: department,
      attributes: ['department_id', ['name', 'department_name']]
    }
  },
  attributes: []
});

const data = query.categories.map(
      ({ category_id, category_name, department }) => ({
        category_id,
        category_name,
        department_id: department.department_id,
        department_name: department.department_name
      })
    );

ctx.body = data;

The resulting output was:

"data": [
    {
        "category_id": 1,
        "department_id": 1
    }
]

After observing some discrepancies, I slightly adjusted the return values which resulted in:

({ category_id, category_name, department }) => ({
        // category_id,
        // category_name,
        department_id: department.department_id,
        department_name: department.department_name
      })

This led to the following JSON response:

"data": [
    {
        "department_id": 1
    }
]

Alternatively, by omitting department_id and department_name:

({ category_id, category_name, department }) => ({
        category_id,
        category_name,
        // department_id: department.department_id,
        // department_name: department.department_name
      })

The JSON output was:

"data": [
    {
        "category_id": 1
    }
]

I couldn't find another way to manipulate the data.

Second Attempt

await product
.findOne({
  where: { product_id: id },
  include: {
    model: category,
    attributes: ['category_id', ['name', 'category_name']],
    include: {
      model: department,
      attributes: ['department_id', ['name', 'department_name']]
    }
  },
  attributes: []
})
.then(query => {
  const data = query.categories.map(
    ({ category_id, category_name, department }) => ({
      category_id,
      category_name,
      department_id: department.department_id,
      department_name: department.department_name
    })
  );

  ctx.body = data;
});

Both approaches yielded similar results, leaving me uncertain on how to proceed.

---

I tried mapping variables within nested arrays containing JSON data, which produced the desired outcome:

const data = {
  categories: [
    {
      category_id: 1,
      category_name: 'French',
      department: { department_id: 1, department_name: 'Regional' },
      product_category: { product_id: 1, category_id: 1 }
    }
  ]
};

const product = data.categories.map(
  ({ category_id, category_name, department }) => ({
    category_id,
    category_name,
    department_id: department.department_id,
    department_name: department.department_name
  })
);

console.log(product);

// [ { category_id: 1,
//    category_name: 'French',
//    department_id: 1,
//    department_name: 'Regional' } ]

Despite these efforts, I felt perplexed. How should I handle data obtained from a Sequelize query? Your guidance would be greatly appreciated.

Please inform me if my problem-solving approach is flawed or if you require further details on the model schema.