sequelize.js - Sequelize - 通过动态表单更新记录

Question

对于前面的长篇文章，提前道歉。
我有一个 Cocktail 和通过 Ingredients 关联的 belongsToMany 模型；并且可以通过动态形式进行更新。这个表单允许你添加和删除输入字段，每个字段代表一个 Ingredient 。

在提供用于更新 Ingredients 的代码之前，我想先说我的代码在某种程度上有效......我有兴趣更有效地编写它；同时还修复了一个随机发生的错误。
我不确定是什么触发了错误；但该错误会产生随机结果。有时一切正常，添加新行，编辑行正常更新。但有时新记录会出现在列表的顶部。作为引用，有时屏幕截图中的新成分会首先出现在 Gin 的顶部，而不是底部。
我很感激任何关于修复和改进我的尝试的意见。

// The object passed when making PUT request:
const { id } = req.params (for current cocktail to update)

ingredients = [ {id:'1', ingredient: 'Gin'}, {id:'2', ingredient: 'Lemon'}, 
{id:'3', ingredient: 'Syrup'}, {ingredient: 'New Ingredient'} ]

// You can see that 'New Ingredient' has no id. When looping through the array, any object 
without an id gets created as a new record.   

const cocktailToUpdate = await Cocktail.findByPk(id, {
  include: [
    {
      model: Ingredient,
      as: 'ingredients',
    },
  ],
});

await ingredients.forEach(async (ingredient) => {

      // if object has no id, then create new ingredient creating the 'New Ingredient'
      if (ingredient.id === undefined) {
        const createdIngredient = await Ingredient.create({
          ingredient: ingredient.ingredient,
        });
        await cocktailToUpdate.addIngredients(createdIngredient);
      }

      // if object has id and ingredient, find and update record
      if (ingredient.id && ingredient.ingredient) { 
        const ingredientToUpdate = await Ingredient.findByPk(ingredient.id);
        await ingredientToUpdate.update({
          ingredient: ingredient.ingredient,
        });
      }
      
      // finally loop through the existing Cocktail we are updating, and compare its ingredients with the ingredients passed into the route at the beginning. Ingredients that do not exist in the passed ingredients object are then deleted from the record.
      const unusedIngredients = await cocktailToUpdate.ingredients.filter(
        (ing) => !ingredients.find((i) => ing.id === i.id)
      );
      await unusedIngredients.forEach(async (ing) => {
        const ingredientToDelete = await Ingredient.findByPk(ing.id);
        await ingredientToDelete.destroy();
      });
    });

编辑:添加我的鸡尾酒和成分模型；和关系定义。
鸡尾酒和配料关系定义

Cocktail.belongsToMany(Ingredient, {
  as: 'ingredients',
  through: Cocktail_Ingredient,
  onDelete: 'cascade',
  hooks: 'true',
});
Ingredient.belongsToMany(Cocktail, {
  as: 'ingredients',
  through: Cocktail_Ingredient,
});

鸡尾酒模型

const Cocktail = db.define('Cocktail', {
  id: {
    type: UUID,
    defaultValue: UUIDV4,
    primaryKey: true,
  },
  name: {
    type: STRING,
    allowNull: false,
    validate: {
      notEmpty: true,
    },
  },
  description: {
    type: TEXT,
    allowNull: false,
    validate: {
      notEmpty: true,
    },
  },
  image: {
    type: STRING,
    allowNull: false,
    validate: {
      notEmpty: true,
    },
  },
});

成分模型

const Ingredient = db.define('Ingredient', {
  id: {
    type: UUID,
    defaultValue: UUIDV4,
    primaryKey: true,
  },
  ingredient: {
    type: STRING,
    allowNull: false,
    validate: {
      notEmpty: true,
    },
  },
});

Answer 1

由于您提前知道 Cocktail.id 和每个 Ingredient 的数据，您可以通过为每个记录构建模型实例，然后根据记录状态进行保存/插入，从而大大减少对数据库的 I/O。
还建议使用事务 - 如果出现错误，您可以回滚所有更改。

// use Sequelize.Op query operators
const { Op } = require('sequelize');

// ... code

let transaction;
try {
  // create a new transaction that we will pass into each query
  transaction = await sequelize.transaction();

  // create an array of IDs that already exist
  const idsToUpdate = ingredients
    // remove any elements that don't have the id set
    .filter(ingredient) => ingredient.id)
    // return the ID for each ingredient
    .map((ingredient) => ingredient.id);

  // delete anything we aren't updating
  await Ingredient.destroy({
    where: {
      id: {
        [Op.notIn]: idsToUpdate,
      },
      cocktail_id: id,
    },
    transaction,
  });

  // this will be an array of promises from calling ingredient.save()
  const promises = ingredients.map(ingredient => 
    // build a new ingredient for all the data passed in
    Ingredient.build(
      // set the parent cocktail_id over the other values
      { ...ingredient, cocktail_id: id },
      // specify if is or isn't a new record based on the ingredient.id
      { isNewRecord: !ingredient.id }
    )
    // calling save will return a promise
    .save({ transaction })
  );

  // this will asynchronously resolve the ingredient.save() from the map
  // and return the model instances
  const instances = await Promise.all(promises);

  // commit the changes
  await transaction.commit();
} catch (err) {
  // rollback the transaction if it is set
  if (transaction) {
    await transaction.rollback();
    delete transaction;
  }
  // throw or handle error
  throw err;
}