如何使用node.js控制sequelize中的内部联接查询

Question

天数模型：

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

锻炼锻炼模式：

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

我只想在两张表之间进行内部连接，以返回每天的所有练习，我使用以下

const Days = require('../models/days');
const WorkoutsExercises = require('../models/workoutsExercises');
Days.findAll({
include: [{
model: WorkoutsExercises,
required: true
}]        
})

此函数返回以下查询：

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`; 

如何将开启条件从(day.dayNumber(更改为(day.workoutId(

Answer 1

关系应为以下

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

目标关键字是更改ON子句的内容，我们将使用相同的查询：

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

它给出：

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`;

Answer 2

1. 如果两个模型之间有多个关系，则可以创建多个关联：

   User.hasMany(Task);
   Task.belongsTo(User);
   User.hasMany(Task, {as: "secondAssociation", foreignKey, sourceKey});
   Task.belongsTo(User, {as: "secondAssociation", foreignKey, targetKey});
   

   您可以使用不同的ON子句运行此查询，该子句由foreignKey(和sourceKey/targetKey(值决定，您提供该值来创建关联：

   User.findAll({
   include: [{
   model: Task
   }]
   User.findAll({
   include: [{
   model: Task,
   as: "secondAssociation"
   }]
   })
   

2. 如果要更改给定查询的ON子句，可以使用Model.findAll方法的include[].on选项。

   User.findAll({
   include: [{
   model: Task,
   on: {} //Use Sequelize.Op.col for reference on other column
   }]