sequelize关联查询两种方法

作者: adm 分类: node 发布时间: 2022-04-14 15:54

两张表,博客(blog_content)和分类(blog_type)
blog_content表,包含id,title,content,type_id字段,其中type_id指向blog_type表中的id
blog_type表,包含id,type_name字段
想要查询博客列表时,查询到分类名字

定义模型如下:

//blog模型
module.exports = (app) => {
const { STRING, INTEGER,DATE,Now } = app.Sequelize;
const Blog = app.model.define(
"blog",
{
id: {
type: INTEGER,
primaryKey: true,
autoIncrement: true,
},
title:STRING,
content: STRING,
type_id:INTEGER,
},
{
timestamps: false,
freezeTableName: true,
tableName: "blog_content"
}
);
return Blog;
};

//type模型
module.exports = (app) => {
const { STRING, INTEGER } = app.Sequelize;
const Type = app.model.define(
"type",
{
id: {
type: INTEGER,
primaryKey: true,
autoIncrement: true,
},
type_name:STRING,
},
{
timestamps: false,
freezeTableName: true,
tableName: "blog_type"
}
);
return Type;
};

1.第一种查询方式,在service里面定义关联方式,然后直接查询,如下

const Service = require('egg').Service;
class BlogService extends Service {
async list() {
let blogModel = this.ctx.model.Blog;
let typeModel = this.ctx.model.Type;
//下面是重点,blogModel的type_id,指向typeModel的id
blogModel.belongsTo(typeModel, {
foreginkey: "type_id",
targetkey: "id",
})
let result = await blogModel.findAll({
include: [{ model: typeModel, attributes: ['type_name'] }]
})
return result
}
}
module.exports = BlogService;

2.第二种,在模型里直接定义属于关系,查询时候直接查询,如下

//修改blog模型,在return Blog之前加上如下方法
Blog.associate = function() {
app.model.Blog.belongsTo(app.model.Type, { foreignKey: 'type_id', targetKey: 'id'})
}

//service 查询方式也简化如下
async list() {
let result = await this.ctx.model.Blog.findAll({
include: [{ model: this.ctx.model.Type, attributes: ['type_name'] }]
})
return result
}

如果觉得我的文章对您有用,请随意赞赏。您的支持将鼓励我继续创作!