CODEWITHSUNDEEP
mysqlforeign-keyssequelize.jsassociations
fadingbeat
fadingbeat

Reputation: 433

How to populate table with foreign key values, using sequelize?

I have models: Business, Contributor, Feedback.

I have created relationship between Feedback and Contributor, and Feedback and Business like this:

Feedback.belongsTo(Business)

Feedback.belongsTo(Contributor)

The corresponding foreign key attributes are added to the table Feedback. Question is, how to populate them with IDs coming from Business and Contributor table records?

This approach only gets the first record. If I use findAll(), then I get undefined.

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

Business model:

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

Contributor model:

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

Feedback model: 

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

Upvotes: 1

Views: 1225

Answers (2)

mousto090
mousto090

Reputation: 2039

A Good use case for model streaming but I think sequelize doesn't support it yet

With your approch, using findOne combined with offset option you can create/update the Feedback model like this.

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

If you have many records you should consider using findAll() with offset and limit options, then do a bulkCreate() with updateOnDuplicate option to avoid making many database queries

Upvotes: 1

Anatoly
Anatoly

Reputation: 22803

To get Feedback items with certain attributes call findAll:

var feedback = await Feedback.findAll({
         attributes: ['contributor_id', 'business_id', 'last_scraped']
        })

Upvotes: 0

Related Questions