mysql - 如何使用 sequelize 用外键值填充表？

Question

我有模型:业务、贡献者、反馈。

我已经在反馈和贡献者以及反馈和业务之间创建了这样的关系:

Feedback.belongsTo(Business)

Feedback.belongsTo(Contributor)

相应的外键属性被添加到表反馈中。问题是，如何用来自 Business 和 Contributor 表记录的 ID 填充它们？

这种方法只获取第一条记录。如果我使用 findAll() ，那么我会得到未定义。

for (let assetsUrl of assetUrls) {
  ...
  var businesses = null;
  var reviews = null;
  ...
  var timestamp = Math.floor(Date.now() / 1000);
  var b_id = await Business.findOne({
    attributes: ["id"],
  })
  var c_id = await Contributor.findOne({
  })

    businesses = await Business.upsert({
      ...
      last_scraped: timestamp
    });
    reviews = await Review.upsert(
      {
        contributor_id: c_id.id,
        business_id: b_id.id,
        last_scraped: timestamp,
      },
    )
  }

商业模式:

class Business extends Model {}
Business.init(
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true,
    },
    site: {
      type: Sequelize.STRING,
    },
    name: {
      type: Sequelize.STRING,
    },
    business_type: {
      type: Sequelize.STRING,
      unique: false,
      defaultValue: "",
    },
    address: {
      type: Sequelize.TEXT,
      // allowNull defaults to true
    },
    price: {
      type: Sequelize.STRING,
    },
    url: {
      type: Sequelize.STRING,
      allowNull: false, 
      unique: true,
    },
    last_scraped: {
      type: Sequelize.INTEGER, 
      defaultValue: Math.floor(Date.now() / 1000)
    },
  },
  {
    sequelize,
    modelName: "business",
    timestamps: true,
    createdAt: false,
    updatedAt: false,
    underscored: true
  }
);

Business === sequelize.models.Business;
Business.sync();

贡献者模型:

class Contributor extends Model {}
Contributor.init(
  {
    id: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true,
    },
    site: {
      type: Sequelize.STRING,
    },
    name: {
      type: Sequelize.STRING,
      unique: false,
    },
    location: {
      type: Sequelize.STRING,
      unique: false,
    },
    photo: {
      type: Sequelize.STRING,
      unique: false,
    },
    url: {
      type: Sequelize.STRING,
      allowNull: false, 
      unique: true,
    },
    status: {
      type: Sequelize.SMALLINT,
    },
    last_scraped: {
      type: Sequelize.INTEGER, 
      defaultValue: Math.floor(Date.now() / 1000)
    },
  },
  {
    sequelize,
    modelName: "contributor",
    timestamps: true,
    createdAt: false,
    updatedAt: false,
    underscored: true,
  }
);

Contributor === sequelize.models.Contributor;
Contributor.sync();

反馈模型:

class Feedback extends Model {}
Feedback.init(
  {
    contributor_id: {
      type: Sequelize.INTEGER,
    },
    business_id: {
      type: Sequelize.INTEGER,
    },
    date: {
      type: Sequelize.STRING,
      unique: false,
    },
    rating: {
      type: Sequelize.STRING,
      unique: false,
    },
    content: {
      type: Sequelize.STRING,
      unique: false,
    },
    last_scraped: {
      type: Sequelize.INTEGER, 
      defaultValue: Math.floor(Date.now() / 1000)
    },
  },
  {
    sequelize,
    modelName: "feedback",
    timestamps: true,
    createdAt: false,
    updatedAt: false,
    underscored: true,
  }
);
Feedback.belongsTo(Contributor, { foreignKey: 'contributor_id' })
Feedback.belongsTo(Business, { foreignKey: 'business_id'})
Feedback=== sequelize.models.Review;
Feedback.sync();

Answer 1

模型流的一个很好的用例，但我认为 sequelize doesn't support it yet

使用您的方法，将 findOne 与 offset 选项结合使用，您可以
像这样创建/更新 Feedback 模型。

// Get number of records to avoid unnecessary findOne in the loop
const bRecordCount = await Business.count();
const cRecordCount = await Contributor.count();

for (let i = 0; i < assetUrls.length; i++) {
    const assetsUrl = assetUrls[i];

    // ...
    let bRecord = null;
    let cRecord = null;
    let options = {
        attributes: ["id"],
        // order by id to be sure we get different record each time
        order: [['id', 'ASC']],
        raw: true,
        offset: i //skip already taken records 
    };

    try {

        if (i < bRecordCount && i < cRecordCount) {
            bRecord = await Business.findOne(options)
            cRecord = await Contributor.findOne(options)
        }

        if (bRecord && cRecord) {
            feedback = await Feedback.upsert({
                contributor_id: cRecord.id,
                business_id: bRecord.id,
                last_scraped: timestamp,
                //...
            });
        }
    } catch (err) {
        console.log(err);
    }
}

如果你有很多记录，你应该考虑使用
findAll()
使用 offset 和 limit 选项，
然后做一个 bulkCreate()
使用 updateOnDuplicate 选项避免进行多次数据库查询