egg mysql 连表查询_Egg中使用Sequelize框架关联查询Mysql数据库

Sequelize视频教程_Eggjs仿小米商城企业级Nodejs项目实战视频教程: https://www.itying.com/goods-941.html

一、Sequelize简介

前面的章节中，我们介绍了如何在框架中通过 egg-mysql 插件来访问数据库。而在一些较为复杂的应用中，我们可能会需要一个 ORM 框架来帮助我们管理数据层的代码。而在 Node.js 社区中，sequelize 是一个广泛使用的 ORM 框架，它支持 MySQL、SQLite 和 MSSQL 、PostgreSQL等多个数据源。下面我们主要给大家讲讲sequelize 结合MySQL的使用。

相关文档：

二、Sequelize 操作Mysql数据库

1、安装egg-sequelize以及mysql2

npm install --save egg-sequelize mysql2

2、在 config/plugin.js 中引入 egg-sequelize 插件

exports.sequelize = {

enable: true,

package: 'egg-sequelize',

};

3、 在 config/config.default.js 中编写 sequelize 配置

config.sequelize = {

dialect: 'mysql',

host: '127.0.0.1',

port: 3306,

database: 'test',

username:"root",

password:"123456"

};

三、Sequelize 操作Mysql实现增删改查

1、在app/model/ 目录下编写数据库Model,以用户表user为例

'use strict';

module.exports = app => {

const { STRING, INTEGER, DATE } = app.Sequelize;

const User = app.model.define('user', {

id: { type: INTEGER, primaryKey: true, autoIncrement: true },

name: STRING(30),

age: INTEGER,

created_at: DATE,

updated_at: DATE,

});

return User;

};

或者

'use strict';

module.exports = app => {

const { STRING, INTEGER, DATE } = app.Sequelize;

const User = app.model.define('user', {

id: { type: INTEGER, primaryKey: true, autoIncrement: true },

name: STRING(30),

age: INTEGER,

created_at: DATE,

updated_at: DATE,

},{

timestamps: false, //自动增加创建时间

tableName: 'user_info' //设置表名称

});

return User;

};

2、定义controller实现数据库的增删改查

async index() {

const ctx = this.ctx;

ctx.body = await ctx.model.User.findAll({limit: 10, offset: 0,order:[["id","desc"]]});

//指定返回的字段

//ctx.body = await ctx.model.User.findAll({attributes: ['id', 'name'],limit: 10,order:[["id","desc"]]});

}

async findOne() {

const ctx = this.ctx;

var result = await ctx.model.User.findByPk(106);

ctx.body=result || '';

}

async create() {

const ctx = this.ctx;

const user = await ctx.model.User.create({ name:"张三", age:20 });

ctx.status = 201;

ctx.body = user;

}

async update() {

const ctx = this.ctx;

const id = 106;

const user = await ctx.model.User.findByPk(id);

if (!user) {

ctx.status = 404;

return;

}

await user.update({ name:"李四", age:43 });

ctx.body = user;

}

async destroy() {

const ctx = this.ctx;

const id = 213;

const user = await ctx.model.User.findByPk(id);

if (!user) {

ctx.status = 404;

return;

}

await user.destroy();

ctx.status = 200;

ctx.body="删除成功";

}

四、Sequelize 操作多表关联查询

4.1、1对1 hasOne 或者 belongsTo

ArticleCate.associate = function (){

// 1对1

app.model.ArticleCate.hasOne(app.model.Article, {foreignKey: 'cateId'});

}

查询语句

const { ctx } = this;

let result = await ctx.model.ArticleCate.findAll({

include: {

model: ctx.model.Article

}

});Article.associate = function (){

// 1对1

app.model.Article.belongsTo(app.model.ArticleCate, {foreignKey: 'cateId'});

}

查询语句

const { ctx } = this;

let result = await ctx.model.Article.findAll({

include: {

model: ctx.model.ArticleCate

}

});

4.2、1对多 hasMany

ArticleCate.associate = function (){

// 1对多

app.model.ArticleCate.hasMany(app.model.Article, {foreignKey: 'cateId'});

}

查询语句

const { ctx } = this;

let result = await ctx.model.ArticleCate.findAll({

include: {

model: ctx.model.Article

}

});

4.3、多对多 belongsToMany

model/student.js

module.exports = app => {

const { STRING, INTEGER } = app.Sequelize;

const Student = app.model.define('student', {

id: {

type: INTEGER,

autoIncrement: true,

primaryKey: true

},

name: {

type: STRING,

},

number: {

type: STRING,

allowNull: false,

},

password: {

type: STRING(32),

allowNull: false

}

}, {

timestamps: false,

tableName: 'student'

});

Student.associate = function () {

//一个学生可以选修多门课程

app.model.Student.belongsToMany(app.model.Lesson, {

through: app.model.LessonStudent,

foreignKey: 'studentId',//注意写法

otherKey: 'lessonId'

});

}

return Student;

}

model/lesson.js

module.exports = app => {

const { INTEGER, STRING } = app.Sequelize;

const Lesson = app.model.define('lesson', {

id: {

type: INTEGER,

primaryKey: true,

autoIncrement: true

},

name: {

type: STRING,

allowNull: false

}

},{

timestamps: false,

tableName: 'lesson'

});

Lesson.associate = function () {

//一个学生可以选修多门课程

//一门课程可以被多个学生选修

app.model.Lesson.belongsToMany(app.model.Student, {

through: app.model.LessonStudent,

foreignKey: 'lessonId',//注意写法

otherKey: 'studentId'

});

}

return Lesson;

}

model/lesson_student.js

module.exports = app => {

const { INTEGER } = app.Sequelize;

const LessonStudent = app.model.define('lesson_student', {

lessonId: {

type: INTEGER,

primaryKey: true

},

studentId: {

type: INTEGER,

primaryKey: true

}

},{

timestamps: false,

tableName: 'lesson_student'

});

// LessonStudent.associate = function(){

// }

return LessonStudent;

}

多对多查询数据

'use strict';

const Controller = require('egg').Controller;

class ArticleController extends Controller {

//查询数据 1对1 1对多

async index() {

const { ctx } = this;

// let result = await ctx.model.Article.findAll({

// include: {

// model: ctx.model.ArticleCate

// }

// });

let result = await ctx.model.ArticleCate.findAll({

include: {

model: ctx.model.Article

}

});

ctx.body = result;

}

//查询数据 多对多

async showAll() {

const { ctx } = this;

//课程有哪些学生选修

// let result = await ctx.model.Lesson.findAll({

// include: {

// model: ctx.model.Student

// }

// });

//每个学生选修了哪些课程

let result = await ctx.model.Student.findAll({

include: {

model: ctx.model.Lesson

}

});

ctx.body = result;

}

}

module.exports = ArticleController;