在设计权限接口的时候，创建了3个表，这里的业务实现就是拿表的数据去查另一个表的数据，但是先查一个表得到数据后在去用数据查另一个表这是很笨的方法，所以看了看sequelize文档发现有关联表这么个用法。
什么是关联表
关联表：就是表之间具备关系，就是常说的一对一，一对多 ，多对多的关系。例子就如有两个表，一个是学生信息表，一个是教师信息表，一个学生有多个老师教学，一个老师教多个学生，那么这是多对多的关系。
关联查询
Eager Loading 和 Lazy Loading 的概念是理解在 Sequelize 中获取关联如何工作的基础。延迟加载是指仅在您真正需要时才获取关联数据的技术；另一方面，Eager Loading 是指从一开始就使用更大的查询一次获取所有内容的技术。
一对一使用情景
- 一张用户表
- 一张角色表
- 每个用户只能拥有一个角色
上代码
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
}
}
]
}
]
}
}
复制代码© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
![[桜井宁宁]COS和泉纱雾超可爱写真福利集-一一网](https://www.proyy.com/skycj/data/images/2020-12-13/4d3cf227a85d7e79f5d6b4efb6bde3e8.jpg)
![[桜井宁宁] 爆乳奶牛少女cos写真-一一网](https://www.proyy.com/skycj/data/images/2020-12-13/d40483e126fcf567894e89c65eaca655.jpg)
