4.建立关联表

4-1 创建菜单和角色model

创建RoleAccess model

src/db/models/roleAccess.ts

import { DataTypes, Model, Optional } from 'sequelize'
import seq from '../seq'
export interface RoleAccessModelProps {
  id: number;
  role_id: number;
  access_id: number;
}
interface RoleAccessCreationAttributes extends Optional<RoleAccessModelProps, "id"> {}
interface RoleAccessInstance extends Model<RoleAccessModelProps, RoleAccessCreationAttributes>, RoleAccessModelProps {}
const RoleAccess = seq.define<RoleAccessInstance>('RoleAccess', {
  id: {
    primaryKey: true,
    type: DataTypes.INTEGER,
    autoIncrement: true
  },
  role_id: {
    type: DataTypes.INTEGER.UNSIGNED,
    comment: '外键 关联roles表id'
  },
  access_id: {
    type: DataTypes.INTEGER,
    comment: '外键 关联access表id'
  }
})
export default RoleAccess

4-2 创建用户和角色model

创建userRole model

src/db/models/userRole.ts

import { DataTypes, Model, Optional } from 'sequelize'
import seq from '../seq'
export interface UserRoleModelProps {
  id: number;
  user_id: number;
  role_id: number;
}
interface UserRoleCreationAttributes extends Optional<UserRoleModelProps, "id"> {}
interface UserRoleInstance extends Model<UserRoleModelProps, UserRoleCreationAttributes>, UserRoleModelProps {}
const UserRole = seq.define<UserRoleInstance>('UserRole', {
  id: {
    primaryKey: true,
    type: DataTypes.INTEGER,
    autoIncrement: true
  },
  user_id: {
    type: DataTypes.INTEGER.UNSIGNED,
    comment: '外键 关联user表id'
  },
  role_id: {
    type: DataTypes.INTEGER.UNSIGNED,
    comment: '外键 关联roles表id'
  }
})
export default UserRole

4-3 建立关联

src/db/models/index.ts

import UserModel from './user'
import AccessModel from './access'
import RolesModel from './roles'
import RoleAccessModel from './roleAccess'
import UserRoleModel from './userRole'
// 外键关联 建立从属关系
;(() => {
  /**
   * onDelete onUpdate
   * 官方配置文档 https://www.sequelize.com.cn/core-concepts/assocs#ondelete-%E5%92%8C-onupdate
   * 配置说明 https://blog.csdn.net/yajing8/article/details/73014004
  */
  // RoleMenuModel.role_id = UserModel.id
  RoleAccessModel.belongsTo(RolesModel, {
    // 父表delete、update的时候，子表会delete、update掉关联记录；
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
    foreignKey: 'role_id'
  })
  // RoleMenuModel.role_id = AccessModel.id
  RoleAccessModel.belongsTo(AccessModel, {
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
    foreignKey: 'access_id'
  })
  // 双向关联 有利于双向联表查询
  RolesModel.hasMany(RoleAccessModel, {
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
    foreignKey: 'role_id'
  })
  AccessModel.hasMany(RoleAccessModel, {
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
    foreignKey: 'access_id'
  })
  // UserRoleModel.role_id = RolesModel.id
  UserRoleModel.belongsTo(RolesModel, {
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
    foreignKey: 'role_id'
  })
  // UserRoleModel.role_id = UserModel.id
  UserRoleModel.belongsTo(UserModel, {
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
    foreignKey: 'user_id'
  })
  // 双向关联 有利于双向联表查询
  RolesModel.hasMany(UserRoleModel, {
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
    foreignKey: 'role_id'
  })
  UserModel.hasMany(UserRoleModel, {
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
    foreignKey: 'user_id'
  })
})()
export {
  UserModel,
  AccessModel,
  RolesModel,
  RoleAccessModel,
  UserRoleModel
}

4-4 更新同步model

建立关联后 关联代码可以注释掉 关联一次就行了

npm run db

注意表关联

关联后，父表id删除后 我们也想同时自动删除关联表中数据 需要改下表配置 on Delete 为 cascade

说明文档 外键关联设置 on Delete on Update
https://blog.csdn.net/yajing8/article/details/73014004

方式一 表关联时代码中设置

文档地址：https://www.sequelize.com.cn/core-concepts/assocs#ondelete-%E5%92%8C-onupdate

方式二 直接改表配置

目前最新源码

https://gitee.com/brolly/vue3-admin-server