续集多对多查询问题 [英] Sequelize Many to Many Query Issue

问题描述

所以，我有一个现有的 MySQL 数据库，我试图用 Node 中的 Sequelize 连接它，它有一个 products 表、一个 categories 表和一个 categories_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.

推荐答案

我认为你应该使用 belongsToMany 关联在这里.

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屋！