时间戳

首页 > 解决方案 > Node Sequelize 关系挑战

node.js - Node Sequelize 关系挑战

问题描述

我有三个表（所有关联的模型类名都使用 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'
  //   })
  // }

标签： node.jssequelize.js

解决方案

我们可以在其各自的模型中保持关联。我更喜欢在各自的主表而不是映射表中保持关联。这个想法是将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同一张表的primaryKey。我们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

推荐阅读