Double association with sequelize

Question

I have the following relationship with the sequel

// Tasks.js

sequelize.models.tasks.belongsTo(sequelize.models.users, {
	foreignKey: {
		type: DataTypes.INTEGER,
		field: 'ASSIGNEE',
		allowNull: true
	},
	as: 'assignee'
});

sequelize.models.tasks.belongsTo(sequelize.models.users, {
	foreignKey: {
		type: DataTypes.INTEGER,
		field: 'REPORTER',
		allowNull: true
	},
	as: 'reporter'
});

// Users.js

sequelize.models.users.hasMany(sequelize.models.tasks, { as: 'assignee' });
sequelize.models.users.hasMany(sequelize.models.tasks, { as: 'reporter' });

However, what I need is for my Tasks table to have only 2 columns with FK (ASSIGNEE and REPORTER). The problem is that the sequelize creates these columns, but creates a userid as well.

It is possible to make this relationship between the same models with 2 different columns?

Answer 1

I believe this is not the ideal way to define relationships, describing the foreignKey at this point in time.

Examples of Sequelize often do:

const User = sequelize.define('User') // campo id é default

const Task = sequelize.define('Task', {
  name: DataTypes.STRING, // exemplo
  reporterId: {
    type: DataTypes.INTEGER,
    field: 'REPORTER',
    allowNull: true
  },
  assigneeId: {
    type: DataTypes.INTEGER,
    field: 'ASSIGNEE',
    allowNull: true
  }
})

Task.belongsTo(User, {
  foreignKey: 'reporterId',
  as: 'Reporter'
})

Task.belongsTo(User, {
  foreignKey: 'assigneeId',
  as: 'Assignee'
})

Which results in the possibility to do the following query:

return Task.findAll({
  include: [
    {
      model: User,
      as: 'Reporter'
    },
    {
      model: User,
      as: 'Assignee'
    }
  ]
})
.then(tasksArray => {
  console.log(tasksArray[0])
  // { name: <teste>, Reporter: {id: <userId_1>}, Assignee: {id: <userId_2>}
})

So by answering the question, Sequelize can create a default field for the relationship Task.belongsTo(User) called precisely userId. To avoid this it is necessary to define the field in the model of the Task before establishing the relationship (see my example).

Remembering that it is only necessary to define the relationship of User.hasMany(Task) if performing the query in this sense, working in the same way as the above example.