什么是关联表
关联表：就是表之间具备关系，就是常说的一对一，一对多 ，多对多的关系。例子就如有两个表，一个是学生信息表，一个是教师信息表，一个学生有多个老师教学，一个老师教多个学生，那么这是多对多的关系。
关联查询
一对一使用情景
- 一张用户表
- 一张角色表
- 每个用户只能拥有一个角色
上代码
Lazy Loading延迟加载示例
const router = require('koa-router')();
const Role = require('../modules/role.js').Role; // 角色模型
const Privs = require('../modules/privs.js').Privs; // 权限模型
const User = require('../modules/user.js').User; // 用户模型
const sequelize = require('../mysql/sequelize.js').sequelize;
router.prefix('/role');
Role.hasOne(User);
User.belongsTo(Role);
let id = "";
router.get('/test', async (ctx, next) => {
try{
await sequelize.sync();
let data = await User.findAll({
where: {
id:{
[Op.like]:'%'+id+'%'
}
}
});
const item = await data[0].getRole()
ctx.body = {
"retCode": true,
"resultMsg": null,
"errorCode": null,
"data": {
item
}
}
}catch(e){
//TODO handle the exception
ctx.body = {
"retCode": false,
"resultMsg": null,
"errorCode": e,
"data": {
}
}
}
})
module.exports = router
结果展示
Eager Loading急切加载示例
const router = require('koa-router')();
const Role = require('../modules/role.js').Role; // 角色模型
const Privs = require('../modules/privs.js').Privs; // 权限模型
const User = require('../modules/user.js').User; // 用户模型
const sequelize = require('../mysql/sequelize.js').sequelize;
router.prefix('/role');
Role.hasOne(User);
User.belongsTo(Role);
let id = "";
router.get('/test', async (ctx, next) => {
try{
await sequelize.sync();
let data = await User.findAll({
where: {
id:{
[Op.like]:'%'+id+'%'
}
},
include:Role
});
ctx.body = {
"retCode": true,
"resultMsg": null,
"errorCode": null,
"data": {
data
}
}
}catch(e){
//TODO handle the exception
ctx.body = {
"retCode": false,
"resultMsg": null,
"errorCode": e,
"data": {
}
}
}
})
module.exports = router
结果展示
注意点
- 这里的外键是默认的即关联表名+Id组合，自定义的话是通过
foreignKey
// Option 1
User.hasOne(Role, {
foreignKey: 'myId'
});
Role.belongsTo(User);
// Option 2
User.hasOne(Role, {
foreignKey: {
name: 'myId'
}
});
Role.belongsTo(User);
// Option 3
User.hasOne(Role);
Role.belongsTo(User, {
foreignKey: 'myId'
});
// Option 4
User.hasOne(Role);
Role.belongsTo(User, {
foreignKey: {
name: 'myId'
}
});
一对多使用情景
- 一张用户表
- 一张角色表
- 每个用户可以拥有多个角色
上代码
const router = require('koa-router')();
const Role = require('../modules/role.js').Role;
const Privs = require('../modules/privs.js').Privs;
const User = require('../modules/user.js').User;
const sequelize = require('../mysql/sequelize.js').sequelize;
router.prefix('/role');
User.hasMany(Role);
Role.belongsTo(User);
let id ="";
router.get('/test', async (ctx, next) => {
try{
await sequelize.sync();
let data = await User.findAll({
where: {
id:{
[Op.like]:'%'+id+'%'
}
},
include:Role
});
// let reTime = data[0].updatere.split(" ")[0]
// const time = timeTool.time;
// const date = time.split(" ")[0];
ctx.body = {
"retCode": true,
"resultMsg": null,
"errorCode": null,
"data": {
data
}
}
}catch(e){
//TODO handle the exception
ctx.body = {
"retCode": false,
"resultMsg": null,
"errorCode": e,
"data": {
}
}
}
})
module.exports = router
结果展示
多对多使用情景
- 一张用户表
- 一张角色表
- 每个用户可以有多个角色且每个角色也对应多个用户
上代码
/*
* @Description:
* @Autor: ZF
* @Date: 2021-06-22
* @LastEditors: ZF
* @LastEditTime: 2021-06-22
*/
const router = require('koa-router')();
const Role = require('../modules/role.js').Role;
const Privs = require('../modules/privs.js').Privs;
const User = require('../modules/user.js').User;
const sequelize = require('../mysql/sequelize.js').sequelize;
const timeTool = require('../utils/date.js');
const { Op,DataTypes } = require("sequelize");
router.prefix('/role');
const UserRole = sequelize.define('userRole', {
userId: {
type: DataTypes.BIGINT,
references: {
model: User, // 'Movies' would also work
key: 'id'
}
},
roleId: {
type: DataTypes.BIGINT,
references: {
model: Role, // 'Actors' would also work
key: 'id'
}
}
},{
tableName:'userRole', // 查询的表名
timestamps: false, // 就填写false就行
});
User.belongsToMany(Role,{through:UserRole});
Role.belongsToMany(User,{through:UserRole});
let id = "";
/**
* @description 更新当天浏览人数
*/
router.get('/test', async (ctx, next) => {
try{
await sequelize.sync();
let data = await User.findAll({
where: {
id:{
[Op.like]:'%'+id+'%'
},
},include:Role
});
ctx.body = {
"retCode": true,
"resultMsg": null,
"errorCode": null,
"data": {
data
}
}
}catch(e){
//TODO handle the exception
ctx.body = {
"retCode": false,
"resultMsg": null,
"errorCode": e,
"data": {
}
}
}
})
module.exports = router
结果展示
{
"retCode": true,
"resultMsg": null,
"errorCode": null,
"data": {
"data": [
{
"id": 1,
"username": "admin",
"password": "123456",
"token": "k7zoijigHjVZqDp2xKwKHeNY",
"createtime": null,
"updatetime": null,
"other2": null,
"other3": null,
"roles": [
{
"id": 1,
"name": "系统管理员",
"value": "all",
"userRole": {
"userId": 1,
"roleId": 1
}
}
]
},
{
"id": 2,
"username": "test1",
"password": "123456",
"token": null,
"createtime": "2021-5-18 17:51:53",
"updatetime": null,
"other2": null,
"other3": null,
"roles": [
{
"id": 2,
"name": "test1",
"value": "1;2",
"userRole": {
"userId": 2,
"roleId": 2
}
},
{
"id": 3,
"name": "test4",
"value": "2;3;4",
"userRole": {
"userId": 2,
"roleId": 3
}
}
]
},
{
"id": 3,
"username": "test4",
"password": "123456",
"token": null,
"createtime": "2021-5-18 17:51:53",
"updatetime": "2021-5-18 17:54:57",
"other2": null,
"other3": null,
"roles": [
{
"id": 2,
"name": "test1",
"value": "1;2",
"userRole": {
"userId": 3,
"roleId": 2
}
}
]
}
]
}
}
常见问题FAQ
- 免费下载或者VIP会员专享资源能否直接商用？
- 本站所有资源版权均属于原作者所有，这里所提供资源均只能用于参考学习用，请勿直接商用。若由于商用引起版权纠纷，一切责任均由使用者承担。更多说明请参考 VIP介绍。
- 提示下载完但解压或打开不了？
- 找不到素材资源介绍文章里的示例图片？
- 模板不会安装或需要功能定制以及二次开发？
发表评论
还没有评论，快来抢沙发吧！