Sequelize saving nested objects

user_2853555 0
Viewed 93

I have 3 tables that are a many to many relationship

quoteRequest <-> ProductQuoteRequest <-> Products

These are the models

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' })
  }
}

I have been trying to save a quoteRequest

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 }
  }
});

But I couldn't

I have two questions,

  1. Is it possible to do this operation calling only the create, without looking for product_id before the create?
  2. I also tried to create the records passing the product_id, but I couldn't realize, what I should change in my code? I was able to save the quoteRequest, but not the associated object (product_code_request)?
sequelize.js
1 Answers

Up to now the best solution I found was to solve this with a transaction

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();

}

Hopefully it will be useful for someone else

user_28535550
Related