序列化多对多查询问题 [英] Sequelize Many to Many Query Issue
问题描述
因此,我有一个现有的MySQL数据库,正在尝试与Node中的Sequelize连接,该数据库具有一个products表,一个category表和一个category_products表.我要做的是退货,每个产品都包含其所属的所有类别.这就是我所拥有的:
So, I have an existing MySQL database that I'm trying to connect to with Sequelize in Node that has a products table, a categories table and a categories_products table. What I want to do is return products, with each product containing all of the categories it belongs to. Here's what I've got:
// Declare Product Model
const Product = sequelize.define('products', {
name: Sequelize.STRING,
description: Sequelize.STRING,
single_price: Sequelize.BOOLEAN,
oz_price: Sequelize.FLOAT,
half_price: Sequelize.FLOAT,
quarter_price: Sequelize.FLOAT,
eigth_price: Sequelize.FLOAT,
gram_price: Sequelize.FLOAT,
unit_price: Sequelize.FLOAT
},
{
underscored: true
});
// Declare Category Model
const Category = sequelize.define('categories', {
name: Sequelize.STRING,
parent_id: Sequelize.INTEGER,
picture_file_name: Sequelize.STRING
},
{
underscored: true
});
// Join Table
const ProductCategory = sequelize.define('categories_products', {
product_id: Sequelize.INTEGER,
category_id: Sequelize.INTEGER,
}, {
timestamps: false,
underscored: true
});
// Do this because there is no id column on ProductCategory table
ProductCategory.removeAttribute('id');
Category.hasMany(Category, { as: 'children', foreignKey: 'parent_id' });
ProductCategory.belongsTo(Product);
ProductCategory.belongsTo(Category);
Product.hasMany(ProductCategory);
Category.hasMany(ProductCategory);
使用此设置,我进行如下查询:
Using this setup, I query as follows:
Product.findAll({
include: [{
model: ProductCategory,
include: [ Category ]
}],
where: { active: true },
limit: 10
}).then(prods => {
res.send(prods);
}).catch(err => {
res.status(500).send(err);
});
我拿回我的产品,每个产品都有一个类别的数组,但是每个产品最多只能显示一个类别.我的产品应该有很多类别,但只显示第一个类别.
I get back my products and each one has an array of categories, BUT each product only shows a max of one category. I have products that should have many categories, but it only shows the first.
我错过了什么吗?任何帮助将不胜感激.
Am I missing something? Any help would be greatly appreciated.
推荐答案
I think you should use belongsToMany
association here.
您可以这样定义关联
Product.belongsToMany(Category, { through: ProductCategory, foreignKey: 'product_id' });
Category.belongsToMany(Product, { through: ProductCategory, foreignKey: 'category_id' });
,查询可以是
Product.findAll({
include: [Category]
}).then((res) => {
console.log(res);
})
这篇关于序列化多对多查询问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!