I want to change the data structure returned by the orm query. There are four tables in total. product
,category
are many-to-many relationships, there is a product_category
table that serves as a bridge, and a total of four tables, including thedepartment
table. association is as follows:
// 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'
});
Through the above table structure, the following query is obtained to obtain products corresponding to 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 is as follows:
"data": [
{
"category_id": 1,
"category_name": "French",
"department": {
"department_id": 1,
"department_name": "Regional"
},
"product_category": {
"product_id": 1,
"category_id": 1
}
}
]
I want to make the above data as follows:
"data": [
{
"category_id": 1,
"category_name": "French",
"department_id": 1,
"department_name": "Regional"
}
]
In order to process the data as above, there are two ways to modify the sql-based orm query and to process the product
value in javascript. However, since I learned sql with orm, I did not know the first method, so I decided to use it as the second method.
I made two attempts. Note that the framework uses koa.js. The first is as follows:
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;
Below is the body:
"data": [
{
"category_id": 1,
"department_id": 1
}
]
..?? Something is strange, so I change the return value slightly:
({ category_id, category_name, department }) => ({
// category_id,
// category_name,
department_id: department.department_id,
department_name: department.department_name
})
The json value output is:
"data": [
{
"department_id": 1
}
]
Conversely, you annotated department_id
,department_name
:
({ category_id, category_name, department }) => ({
category_id,
category_name,
// department_id: department.department_id,
// department_name: department.department_name
})
The resulting json value is:
"data": [
{
"category_id": 1
}
]
I could not do it any other way.
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 methods have the same results, so I do not know what to do.
So i map the variable to the nested array with json data. I get the result I want:
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' } ]
So I got confused. How can I process data from a sequelize query? I would really appreciate your help.
Please let me know if the approach to solving the problem is wrong or you need a model schema.
I implemented it in a trivial way. It looks like a complete stomach. It would be great if another person would put the Best Way into the answer.
const getProduct = () => {
const a = query.categories[0];
const b = a.get({ plain: true });
const { category_id, category_name } = b;
const { department_id, department_name } = b.department;
return {
category_id,
category_name,
department_id,
department_name
};
};
ctx.body = getProduct();
Json data output:
"product": {
"category_id": 1,
"category_name": "French",
"department_id": 1,
"department_name": "Regional"
}
The sequelize query will be printed like dataValues: {}, (...)
if you run console.log ()
. If you do this, you will not be able to process your data. So it is THE KEY POINT to process the data after the variable containing the query as follows:
data.get ({plain: true})
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments