How to control the inner join query in sequelize using node.js?

Question

Days model:

workoutId: {
    type: Sequelize.INTEGER,
},
traineeId: {
  type: Sequelize.INTEGER,
  primaryKey: true
},
dayNumber: {
  type: Sequelize.INTEGER,
  primaryKey: true
},
status: {
  type: Sequelize.INTEGER
},

WorkoutsExercises model:

workout_id: {
  type: Sequelize.INTEGER,
  primaryKey: true
},
exercise_name: {
  type: Sequelize.INTEGER,
  primaryKey: true
},
coach_id: {
  type: Sequelize.INTEGER,
  primaryKey: true
}

I just want to make an inner join between the two tables to return all exercises in each day, I use the following

const Days = require('../models/days');
const WorkoutsExercises = require('../models/workoutsExercises');

Days.findAll({
  include: [{
    model: WorkoutsExercises,
      required: true
   }]        
})

And this function returns the following query:

SELECT
  `day`.`workoutId`,
  `day`.`dayNumber`,
  `day`.`status`,
  `day`.`traineeId`,
  
  `workoutsExercises`.`workout_id` AS `workoutsExercises.workout_id`, 
  `workoutsExercises`.`exercise_name` AS `workoutsExercises.exercise_name`, 
  `workoutsExercises`.`coach_id` AS `workoutsExercises.coach_id`

FROM `days` AS `day`

INNER JOIN `workoutsExercises` AS `workoutsExercises` 

ON `day`.`dayNumber` = `workoutsExercises`.`workout_id`; 

how can I change the on condition from (day.dayNumber) to (day.workoutId)

Answer 1

The relation should be as the following

WorkoutExercises.hasMany(Day, {foreignKey: 'workout_id'})
Day.belongsTo(WorkoutExercises, {foreignKey: 'workout_id', targetKey: 'workout_id'})

the target key is what changes the ON clause, and we will use the same query:

Days.findAll({
  include: [{
    model: WorkoutsExercises,
    required: true
  }]        
})

which gives:

SELECT

  `day`.`dayNumber`,
  `day`.`dayDate`,
  `day`.`status`,
  `day`.`traineeId`, 
  `day`.`workoutId`, 

  `workoutsExercises`.`exercise_name` AS `workoutsExercises.exercise_name`,
  `workoutsExercises`.`workout_id` AS `workoutsExercises.workout_id`,
  `workoutsExercises`.`coach_id` AS `workoutsExercises.coach_id`
 
 FROM `days` AS `day`
 
 INNER JOIN `workoutsExercises` AS `workoutsExercises`
 
 ON `day`.`workout_id` = `workoutsExercises`.`workout_id`;