解决 Sequelize 在 belongsToMany 关联中的独特约束 [英] Work around Sequelize’s unique constraints in belongsToMany associations

问题描述

我在我的项目中使用 Sequelize.这是两个模型:

I'm using Sequelize in my project. These are the two models:

const User = db.define('user', {
  name: Sequelize.STRING,
  password: Sequelize.STRING
})
const Product = db.define('product', {
  name: Sequelize.STRING,
  price: Sequelize.INTEGER
})

现在用户可以购买产品，我有如下关联设置:

Now users can purchase products and I have associations setup like below:

Product.belongsToMany(User, {through: 'UserProducts'})
User.belongsToMany(Product, {through: 'UserProducts'})

我还有这个带有附加列的 UserProducts 表.

I also have this UserProducts table with an additional column.

const UserProducts = db.define('UserProducts', {
  status: Sequelize.STRING
})

Sequelize 创建一个包含 userId 和 productId 组合的复合键，并且只允许一个包含 userId 和 productId 组合的记录.因此，例如，userId 2 和 productId 14.

Sequelize creates a composite key with combination of userId and productId and will only allow one record with a combination of userId and productId. So, for example, userId 2 and productId 14.

这对我来说是个问题，因为有时人们想要多次购买.我需要以下场景之一才能工作:

This is a problem for me because sometimes people want to purchase multiple times. I need one of the following scenarios to work:

1. 不要使用复合键，而是在 UserProducts 中使用一个全新的自动增量列作为键.

1. Don't use the composite key and instead have a completely new auto-increment column used as key in UserProducts.

不要单独使用 userId 和 productId 来创建键，请允许我在键中再添加一列，例如 status，以便唯一键是三者的组合.

Instead of making key with userId and productId alone, allow me to add one more column into the key such as the status so that unique key is a combination of the three.

我确实想使用关联，因为它们提供了许多强大的方法，但希望更改唯一键以使我可以添加具有相同用户 ID 和产品 ID 组合的多行.

I do want to use the associations as they provide many powerful methods, but want to alter the unique key to work in such a way that I can add multiple rows with the same combination of user id and product id.

由于我的模型/数据库已经在运行，我需要利用迁移来进行此更改.

And since my models/database is already running, I will need to make use of migrations to make this change.

非常感谢您对此提供任何帮助.

Any help around this is highly appreciated.

推荐答案

Belongs-To-Many 在主键不存在于直通模型上时创建唯一键.

Belongs-To-Many creates a unique key when the primary key is not present on through model.

由于您的 UserProducts 表中还有其他属性，因此您需要为 UserProducts 定义模型，然后告诉 sequelize 使用该模型而不是创建自己的模型

Since you also have additional property in your UserProducts table, you need to define the model for UserProducts and then tell the sequelize to use that model instead of creating its own

class User extends Model {}
User.init({
    name: Sequelize.STRING,
    password: Sequelize.STRING
}, { sequelize })

class Product extends Model {}
ProjProductect.init({
    name: Sequelize.STRING,
    price: Sequelize.INTEGER
}, { sequelize })

class UserProducts extends Model {}
UserProducts.init({
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  status: DataTypes.STRING
}, { sequelize })

User.belongsToMany(Project, { through: UserProducts })
Product.belongsToMany(User, { through: UserProducts })

参考:Sequelize v4 belongsToMany

更新

由于您使用的是 v3 并且已经为您的 UserProducts 表创建了一个模型，因此请使用以下代码段

since you are using v3 and already have a model created for your UserProducts table use following snippet

UserProducts = db.define('UserProducts', {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  status: DataTypes.STRING
})

这篇关于解决 Sequelize 在 belongsToMany 关联中的独特约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！