节点续集关系挑战

Question

我有三个表(所有相关的模型类名都使用PascalCase(

schools                school_codes              course
------                 ------                    ------
id (pk)                code (pk)                 name
name                   school_id (fk)            school_code (fk)

我正在尝试定义序列化关系，以便本课程查找返回相关的学校：

const courseWithSchool = await models.Course.findOne({
include: [{
model: models.School,
required: true,
}],
})

mysql对此非常简单。

mysql> select c.*, s.* from courses c inner join school_codes sc on c.school_code = sc.code inner join schools s on s.id = sc.school_id;

如何定义序列化模型中的关系(不修改现有模式(？谢谢

以下是我的模型定义：

学校.js

module.exports = (sequelize, DataTypes) => {
const School = sequelize.define('School', {
name: DataTypes.STRING,
}, { underscored: true, freezeTableName: true, tableName: 'schools' })  
return School
}

course.js

module.exports = (sequelize, DataTypes) => {
const Course = sequelize.define('Course', {
id: {
type: DataTypes.STRING,
primaryKey: true,
},
name: DataTypes.STRING,
school_code: {
type: DataTypes.STRING,  
references: {
model: 'school_codes',
key: 'code',
}
}
}, { underscored: true, freezeTableName: true, tableName: 'courses' })  
return Course
}

schoolcode.js

module.exports = (sequelize, DataTypes) => {
const SchoolCode = sequelize.define('SchoolCode', {
code:{
type: DataTypes.STRING,
primaryKey: true,
references: {
model: 'courses',  
key: 'school_code'
}
},
school_id: {
type: DataTypes.INTEGER,  
references: {
model: 'schools',  
key: 'id',
},    
},    
}, { underscored: true, freezeTableName: true, tableName: 'school_codes', })     
return SchoolCode
}

我只是在寻找添加到每个模型定义底部的关系-例如

// School.associate = function (models) {
//   School.belongsToMany(models.Course, {
//     through: 'school_codes',
//     foreignKey: 'school_id',
//     otherKey: 'code'
//   })
// }

Answer 1

我们可以在其各自的模型中保持关联。我更喜欢在各自的主表中保持关联，而不是映射表。其思想是将source模型与target模型及其在两个方向上的关系相关联。例如，假设源模型School有一个SchoolCode目标模型及其反向关系

//school.model.js
module.exports = (sequelize, DataTypes) => {
const School = sequelize.define('school', {
name: DataTypes.STRING,
}, { underscored: true, freezeTableName: true, tableName: 'schools' })  

School.associate = function ({SchoolCode, Course}) {
School.hasOne(SchoolCode, {
foreignKey: 'school_id',
})
SchoolCode.belongsTo(School, {foreignKey: 'school_id'})
School.belongsToMany(Course, { through: SchoolCode , foreignKey : 'school_id'}); //added new
}
return School;
}
//course.model.js 
module.exports = (sequelize, DataTypes) => {
const Course = sequelize.define('course', {
id: {
type: DataTypes.STRING,
primaryKey: true,
},
name: DataTypes.STRING,
school_code: {
type: DataTypes.STRING,  
references: {
model: 'school_codes',
key: 'code',
}
}
}, { underscored: true, freezeTableName: true, tableName: 'courses' })  

Course.associate = function ({SchoolCode, School}) {
Course.hasMany(SchoolCode, {
foreignKey: 'code',
})
Course.belongsToMany(School, { through: SchoolCode,  foreignKey : 'code'}); //added new
}
return Course;
}

最后给出了SchoolCode的第三个模型(映射表(。请注意，我们不必添加引用school_code。它是同一个表的主键code。我们主要使用references来定义外键，这里不需要反向定义。因此评论了下面代码中的部分。

module.exports = (sequelize, DataTypes) => {
const SchoolCode = sequelize.define('SchoolCode', {
code:{
type: DataTypes.STRING,
primaryKey: true,
// references: {
//   model: 'courses',  
//   key: 'school_code'
// }
},
school_id: {
type: DataTypes.INTEGER,  
references: {
model: 'school',  
key: 'id',
},    
},    
}, { underscored: true, freezeTableName: true, tableName: 'school_codes', }) 

return SchoolCode
}

参考文献：https://sequelize.org/master/manual/assocs.html

Answer 2

您可以定义类似的关系

SchoolCode.belongsTo(School, { foreignKey: 'school_id', targetKey: 'id' });
Course.belongsTo(SchoolCode, { foreignKey: 'school_code', targetKey: 'code' });