When I was designing the permissions interface, I created 3 tables. The business implementation here was to use the data from one table to query the data from another table. However, it was a stupid way to query the data from one table and then use the data from another table.

What is an associative table

Associative table: is the relationship between the tables, is often said to be one-to-one, one-to-many, many-to-many relationship. For example, if there are two tables, one is a student information table, one is a teacher information table, a student has multiple teachers, a teacher teaches multiple students, then this is a many-to-many relationship.

Associated query

The concepts of Eager Loading and Lazy Loading are fundamental to understanding how getting associations in Sequelize works. Lazy loading is the technique of getting linked data only when you really need it; Eager Loading, on the other hand, is the technique of getting everything at once from the start, using larger queries.

One-to-one usage scenario

  • A list of users
  • A list of roles
  • Each user can have only one role

In the code

Lazy Loading Example of Lazy Loading

const router = require('koa-router') ();const Role = require('.. /modules/role.js').Role;         // Role model
const Privs = require('.. /modules/privs.js').Privs;      // Permission model
const User = require('.. /modules/user.js').User;         // User model
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]:The '%'+id+The '%'}}});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
Copy the code
The results show

Eager Loading example

const router = require('koa-router') ();const Role = require('.. /modules/role.js').Role;         // Role model
const Privs = require('.. /modules/privs.js').Privs;      // Permission model
const User = require('.. /modules/user.js').User;         // User model
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]:The '%'+id+The '%'}},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
Copy the code
The results show

Pay attention to the point

  • The default foreign key is the associated table name +Id combination, custom is passedforeignKey
// 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'}});Copy the code

One to many usage scenario

  • A list of users
  • A list of roles
  • Each user can have multiple roles

In the code

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]:The '%'+id+The '%'}},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
Copy the code
The results show

Many-to-many scenarios

  • A list of users
  • A list of roles
  • Each user can have multiple roles and each role corresponds to multiple users

In the code

/* * @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'.// Query the table name
	timestamps: false.// Just fill in false
});
User.belongsToMany(Role,{through:UserRole});
Role.belongsToMany(User,{through:UserRole});
let id = "";
/ * * *@description Update number of views */
router.get('/test'.async (ctx, next) => {
	try{
            await sequelize.sync();
            let data = await User.findAll({
                where: {
                    id:{
                        [Op.like]:The '%'+id+The '%'}},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
Copy the code
The results show
{
    "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": "System Administrator"."value": "all"."userRole": {
                            "userId": 1."roleId": 1}}]}, {"id": 2."username": "test1"."password": "123456"."token": null."createtime": "The 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": "The 2021-5-18 17:51:53"."updatetime": "The 2021-5-18 17:54:57"."other2": null."other3": null."roles": [{"id": 2."name": "test1"."value": "1. 2. ""."userRole": {
                            "userId": 3."roleId": 2}}]}}Copy the code