代码之家 › 专栏 › 技术社区 › agusgambina

Sequelize保存嵌套对象

sequelize.js

agusgambina · 技术社区 · 3 年前

我有3张表,它们是多对多的关系

quoteRequest <-> ProductQuoteRequest <-> Products

这些是模型

module.exports = class QuoteRequest extends Model {
  static init(sequelize) {
    return super.init({
      id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        unique: true,
        autoIncrement: true,
        primaryKey: true
      },
      requester: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: false
      },
      notes: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: false
      },
      is_deleted: {
        // boolean NOT NULL DEFAULT FALSE
        type: DataTypes.BOOLEAN,
        allowNull: false,
        defaultValue: false
      },
      creation_date: {
        // timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
        type: DataTypes.DATE
      },
      last_modification_date: {
        // timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
        type: DataTypes.DATE
      }
    }, {
      sequelize,
      modelName: 'QuoteRequest',
      tableName: 'quote_requests'
    })
  }

  static associate(sequelize) {
    this.product_quote_requests = this.hasMany(sequelize.ProductsQuoteRequests, { foreignKey: 'id' })
  }

}


module.exports = class ProductQuoteRequest extends Model {
  static init(sequelize) {
    return super.init({
      id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        unique: true,
        autoIncrement: true,
        primaryKey: true
      },
      product_id: {
        type: DataTypes.INTEGER,
        model: Product,
        key: 'id'
      },
      quote_request_id: {
        type: DataTypes.INTEGER,
        model: QuoteRequest,
        key: 'id'
      },
      quantity: {
        type: DataTypes.INTEGER,
        allowNull: false,
        unique: false
      },
      is_deleted: {
        type: DataTypes.BOOLEAN,
        allowNull: false,
        defaultValue: false
      },
      creation_date: {
        type: DataTypes.DATE
      },
      last_modification_date: {
        type: DataTypes.DATE
      }
    }, {
      sequelize,
      modelName: 'ProductQuoteRequest',
      tableName: 'products_quote_requests'
    })
  }
  static associate(sequelize) {
    this.product = this.belongsTo(sequelize.Products, { foreignKey: 'product_id' })
    this.quote_request = this.belongsTo(sequelize.QuoteRequests, { foreignKey: 'quote_request_id' })
  }
}

module.exports = class Product extends Model {
  static init(sequelize) {
    return super.init({
      id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        unique: true,
        primaryKey: true
      },
      code: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true
      },
      description: {
        type: DataTypes.STRING,
        allowNull: true,
        unique: false
      },
      position: {
        type: DataTypes.INTEGER,
        allowNull: false,
        unique: true
      },
      is_enable: {
        type: DataTypes.BOOLEAN,
        allowNull: false,
        defaultValue: false
      },
      category: {
        type: DataTypes.ENUM('Buje', 'Soporte', 'Bieleta', 'Cazoleta', 'Kit', 'Vario'),
        allowNull: false,
        defaultValue: false
      },
      filename: {
        type: DataTypes.STRING,
        allowNull: true,
        unique: false
      },
      is_deleted: {
        type: DataTypes.BOOLEAN,
        allowNull: false,
        defaultValue: false
      },
      creation_date: {
        type: DataTypes.DATE
      },
      last_modification_date: {
        type: DataTypes.DATE
      }
    },{
      sequelize,
      modelName: 'Product',
      tableName: 'products'
    })
  }

  static associate(sequelize) {
    this.items = this.hasMany(sequelize.Items, { foreignKey: 'product_id' })
    this.product_quote_request = this.hasMany(sequelize.ProductsQuoteRequests, { foreignKey: 'product_id' })
  }
}

我一直在尝试保存报价请求

await models.QuoteRequests.create({
  requester: 'mailtodelete@example.com',
  notes: '',
  product_quote_request: "products": [{
      "quantity": 10,
      product: { "code": "I100" }
  },
  {
      "quantity": 20,
      product: { "code": "C001" }
  }],
  }, {
  include: {
    model: models.ProductsQuoteRequests,
    as: 'product_quote_request',
    include: { models.Product }
  }
});

但我做不到

我有两个问题,

是否可以只调用 create ,无需查找 product_id 在创建之前?
我还试图创建传递product_id的记录,但我没有意识到,我应该在代码中更改什么?我能够保存quoteRequest,但不能保存关联的对象(product_code_request)?

0 回复 | 直到 3 年前

agusgambina 3 年前

到目前为止,我找到的最好的解决方案是通过事务来解决这个问题

let transaction;

try {
  transaction = await db.sequelize.transaction();

  // step 1
  // Retrieve products ids
  const requestedProducts = await Promise.all(products.map(async (product) => {
    const p = await getProductByCode(product.productCode, transaction);
    return await { product_id: p.id, quantity: product.quantity };
  }))
  // console.log('*** step 1 retrieve products ids *** ', requestedProducts);

  // step 2
  // Create the quote request record
  const quoteRequest = await models.QuoteRequests.create({
    requester: clientEmail,
  }, { transaction });

  // step 3
  // Create the requested products records
  await Promise.all(requestedProducts.map(async (requestedProduct) => {
    requestedProduct.quote_request_id = quoteRequest.id;
    return await models.ProductsQuoteRequests.create(requestedProduct, { transaction });
  }));
  await transaction.commit();

} catch (err) {
  console.log('error ', err)
  if (transaction) await transaction.rollback();

}

希望它对其他人有用