如何在 Sequelize 中配置一对多关系?

2022-01-19 00:00:00 node.js express mysql sequelize.js

我目前正在使用 Express 和 Sequelize + MySQL，我想知道解决这个问题的最佳方法是什么.如果这是一个基本问题，我深表歉意，因为我对 Sequelize 甚至 SQL 数据库都很陌生.

I'm currently using Express with Sequelize + MySQL and was wondering what the best approach for me to solve this problem was. I apologise if this is a basic question as I'm pretty new to Sequelize and even SQL databases in general.

我有一个像这样的模型User；

I have a model User like so;

export default db.define('User', {
  id: {
    type: Sequelize.INTEGER,
    allowNull: false,
    primaryKey: true,
    autoIncrement: true,
  },
  name: {
    type: Sequelize.STRING,
    allowNull: false,
  },
  email: {
    type: Sequelize.STRING,
    allowNull: false,
  },
  createdAt: {
    type: Sequelize.DATE,
    defaultValue: Sequelize.NOW,
  },
  updatedAt: {
    type: Sequelize.DATE,
    defaultValue: Sequelize.NOW,
  },
});

那我还有另一个型号Shoe像这样；

Then I also have another model Shoe like so;

export default db.define('Shoe', {
  id: {
    type: Sequelize.INTEGER,
    allowNull: false,
    primaryKey: true,
    autoIncrement: true,
  },
  name: {
    type: Sequelize.STRING,
    allowNull: false,
  },
  size: {
    type: Sequelize.INTEGER,
    allowNull: true,
  },
  quality: {
    type: Sequelize.ENUM('Brand New', 'Used', 'Collector Item'),
    allowNull: false,
  },
  createdAt: {
    type: Sequelize.DATE,
    defaultValue: Sequelize.NOW,
  },
  updatedAt: {
    type: Sequelize.DATE,
    defaultValue: Sequelize.NOW,
  },
});

然后我定义这两者之间的关联.

I then define an association between these two.

User.hasMany(Shoe);
Shoe.belongsTo(User);

但是，这样做的问题是它创造"了鞋子，这会导致重复.我基本上想要一个名为Shoes"的表，其中包含鞋子，然后用户只需根据它拥有的鞋子引用 id.

However, the problem with this is that it 'creates' the shoe and this leads to duplication. I basically want a table called "Shoes" that has the shoes in it, and then the user just references the ids based on what shoes it has.

不确定执行此操作的最佳方法是什么，尤其是在用户拥有多双鞋的情况下.在伪中，我想我想要像 shoes: [1, 2, 3, 4] 这样的鞋子 id 数组，当被查询时会以某种方式在鞋子表中查找并插入用户响应.

Not sure what the best way to do this is, especially if a user owns multiple shoes. In pseudo I suppose I'd want something like an array of shoe id's such as shoes: [1, 2, 3, 4] that when queried then get looked up in the shoes table somehow and inserted into the User response.

显然我可以在原始 SQL 中执行类似的操作，但鉴于 Sequelize 的强大功能，我认为必须有更好的方法来解决此问题.

Obviously I can do something like that in raw SQL, but I figure there must be a better way to approach this given how much power Sequelize has.

希望有人可以就此提供一些建议和帮助！

Hopefully someone can provide some advice and help regarding this!

推荐答案

  1. 我建议使用 tableName 选项.

  2. 如果您想存储用户、鞋子以及每个用户拥有的鞋子的信息，您需要创建第三个表.这个 n:m 关联，因为每个用户可能有几只鞋子，而每只鞋子可能属于几个用户.您可以通过多种方式创建此表:

  1. I recommend use tableName option.

  2. If you want store Users, Shoes and info about what shoe each user have, you need create third table. This n:m association, because each user may have several shoes, and each shoe may belongs to several users. You can create this table by several ways:

  • 参见 Model.belongsToMany 教程 和 api 文档

User.belongsToMany(Shoe, {through: 'UserShoe'});
Shoe.belongsToMany(User, {through: 'UserShoe'});

  • 定义 UserShoe 模型，然后关联 User、Shoe 和 UserShoe 模型:

  • define UserShoe model and then associate User, Shoe and UserShoe models:

    export default db.define('UserShoe', {
  userId: {
    type: Sequelize.INTEGER,
    allowNull: false,
    primaryKey: true
  },
  shoeId: {
    type: Sequelize.INTEGER,
    allowNull: false,
    primaryKey: true
  }
});


User.belongsToMany(Shoe, {through: UserShoe});
Shoe.belongsToMany(User, {through: UserShoe});

  • 或者像这样:

  • or like this:

    User.hasMany(UserShoe);
UserShoe.belongsTo(User);
Shoe.hasMany(UserShoe);
UserShoe.belongsTo(Shoe);

    • 相关文章