.联表查询
一对一
在controller里面如下写
// 获取学生信息 通过一对多的联系
async info(){
const { ctx, app } = this;
let result = await app.model.Student.findAll({
include: {
model: app.model.Info
}
});
ctx.body = result;
}
获取到的值如下:
[
// 第一个学生
{
"id": 1,
"number": "160101",
"password": "202cb962ac59075b964b07152d234b70",
"classId": 1,
"createdAt": "2019-05-12 13:16:09",
"updatedAt": "2019-05-12 13:16:12",
"deletedAt": null,
"info": { // 联表查到的信息
"sex": "男",
"id": 1,
"name": "许仙",
"age": 23,
"studentId": 1,
"createdAt": "2019-05-12 13:25:58",
"updatedAt": "2019-05-12 13:26:01",
"deletedAt": null
}
},
// 第二个学生
{
"id": 2,
"number": "160201",
"password": "202cb962ac59075b964b07152d234b70",
"classId": 2,
"createdAt": "2019-05-12 13:16:32",
"updatedAt": "2019-05-12 13:16:35",
"deletedAt": null,
"info": {
"sex": "女",
"id": 2,
"name": "白素贞",
"age": 20,
"studentId": 2,
"createdAt": "2019-05-12 13:26:41",
"updatedAt": "2019-05-12 13:26:46",
"deletedAt": null
}
},
{
"id": 3,
"number": "160102",
"password": "202cb962ac59075b964b07152d234b70",
"classId": 1,
"createdAt": "2019-05-12 13:17:17",
"updatedAt": "2019-05-12 13:17:21",
"deletedAt": null,
"info": {
"sex": "男",
"id": 3,
"name": "法海",
"age": 22,
"studentId": 3,
"createdAt": "2019-05-12 13:27:20",
"updatedAt": "2019-05-12 13:27:22",
"deletedAt": null
}
},
{
"id": 4,
"number": "160103",
"password": "202cb962ac59075b964b07152d234b70",
"classId": 1,
"createdAt": "2019-05-12 13:17:51",
"updatedAt": "2019-05-12 13:17:54",
"deletedAt": null,
"info": {
"sex": "女",
"id": 4,
"name": "小青",
"age": 18,
"studentId": 4,
"createdAt": "2019-05-12 13:27:48",
"updatedAt": "2019-05-12 13:27:51",
"deletedAt": null
}
},
{
"id": 5,
"number": "160104",
"password": "202cb962ac59075b964b07152d234b70",
"classId": 1,
"createdAt": "2019-05-12 13:18:13",
"updatedAt": "2019-05-12 13:18:16",
"deletedAt": null,
"info": {
"sex": "女",
"id": 5,
"name": "金如意",
"age": 20,
"studentId": 5,
"createdAt": "2019-05-12 13:28:34",
"updatedAt": "2019-05-12 13:28:37",
"deletedAt": null
}
},
{
"id": 6,
"number": "160202",
"password": "202cb962ac59075b964b07152d234b70",
"classId": 2,
"createdAt": "2019-05-12 13:18:36",
"updatedAt": "2019-05-12 13:18:39",
"deletedAt": null,
"info": {
"sex": "男",
"id": 6,
"name": "景松",
"age": 23,
"studentId": 6,
"createdAt": "2019-05-12 13:30:07",
"updatedAt": "2019-05-12 13:30:10",
"deletedAt": null
}
}
]
一对多
// 获取班级名为 软件工程1601 的班级学生
async student(){
const { ctx, app } = this;
let result = await app.model.Classes.findAll({
where: {
name: '软件工程1601'
},
include: {
model: app.model.Student
}
})
ctx.body = result;
}
获取数据如下:
[
{
"id": 1,
"name": "软件工程1601",
"createdAt": "2019-05-12 13:11:43",
"updatedAt": "2019-05-12 13:11:47",
"deletedAt": null,
"students": [
{
"id": 1,
"number": "160101",
"password": "202cb962ac59075b964b07152d234b70",
"classId": 1,
"createdAt": "2019-05-12 13:16:09",
"updatedAt": "2019-05-12 13:16:12",
"deletedAt": null
},
{
"id": 3,
"number": "160102",
"password": "202cb962ac59075b964b07152d234b70",
"classId": 1,
"createdAt": "2019-05-12 13:17:17",
"updatedAt": "2019-05-12 13:17:21",
"deletedAt": null
},
{
"id": 4,
"number": "160103",
"password": "202cb962ac59075b964b07152d234b70",
"classId": 1,
"createdAt": "2019-05-12 13:17:51",
"updatedAt": "2019-05-12 13:17:54",
"deletedAt": null
},
{
"id": 5,
"number": "160104",
"password": "202cb962ac59075b964b07152d234b70",
"classId": 1,
"createdAt": "2019-05-12 13:18:13",
"updatedAt": "2019-05-12 13:18:16",
"deletedAt": null
}
]
}
]
多对多
从学生获取课程信息
// 获取学生的选课内容
async lession(){
const { ctx, app } = this;
let result = await app.model.Student.findAll({
where:{
id: 1,
},
include: [
{model: app.model.Info},
{model: app.model.Lession}
]
});
ctx.body = result;
}
这里的话,注意include的值为一个数组了,这样可以多个联表获取数据
数据如下:
[
{
"id": 1,
"number": "160101",
"password": "202cb962ac59075b964b07152d234b70",
"classId": 1,
"createdAt": "2019-05-12 13:16:09",
"updatedAt": "2019-05-12 13:16:12",
"deletedAt": null,
"info": {
"sex": "男",
"id": 1,
"name": "许仙",
"age": 23,
"studentId": 1,
"createdAt": "2019-05-12 13:25:58",
"updatedAt": "2019-05-12 13:26:01",
"deletedAt": null
},
"lessions": [
{
"id": 1,
"name": "计算机网络",
"createdAt": "2019-05-12 13:12:32",
"updatedAt": "2019-05-12 13:12:35",
"deletedAt": null,
"lession_student": {
"lessionId": 1,
"studentId": 1,
"createdAt": "2019-05-12 13:20:35",
"updatedAt": "2019-05-12 13:20:40",
"deletedAt": null
}
},
{
"id": 2,
"name": "Java程序设计",
"createdAt": "2019-05-12 13:12:50",
"updatedAt": "2019-05-12 13:12:52",
"deletedAt": null,
"lession_student": {
"lessionId": 2,
"studentId": 1,
"createdAt": "2019-05-12 13:23:10",
"updatedAt": "2019-05-12 13:23:13",
"deletedAt": null
}
},
{
"id": 3,
"name": "软件项目管理",
"createdAt": "2019-05-12 13:13:07",
"updatedAt": "2019-05-12 13:13:10",
"deletedAt": null,
"lession_student": {
"lessionId": 3,
"studentId": 1,
"createdAt": "2019-05-12 13:24:21",
"updatedAt": "2019-05-12 13:24:24",
"deletedAt": null
}
}
]
}
]
从课程获取选课学生:
// 获取某个课的参课学生
async lessionStudent(){
const { ctx, app } = this;
let result = await app.model.Lession.findAll({
where:{
name: '网络安全'
},
include: {
model: app.model.Student,
include: {
model: app.model.Info
}
}
});
ctx.body = result;
}
这里注意,在include的下面又有一个include,第二个include是相对Student表的
数据如下:
[
{
"id": 4,
"name": "网络安全",
"createdAt": "2019-05-12 13:13:22",
"updatedAt": "2019-05-12 13:13:25",
"deletedAt": null,
"students": [
{
"id": 2,
"number": "160201",
"password": "202cb962ac59075b964b07152d234b70",
"classId": 2,
"createdAt": "2019-05-12 13:16:32",
"updatedAt": "2019-05-12 13:16:35",
"deletedAt": null,
"lession_student": {
"lessionId": 4,
"studentId": 2,
"createdAt": "2019-05-12 13:24:59",
"updatedAt": "2019-05-12 13:25:03",
"deletedAt": null
},
"info": {
"sex": "女",
"id": 2,
"name": "白素贞",
"age": 20,
"studentId": 2,
"createdAt": "2019-05-12 13:26:41",
"updatedAt": "2019-05-12 13:26:46",
"deletedAt": null
}
},
{
"id": 6,
"number": "160202",
"password": "202cb962ac59075b964b07152d234b70",
"classId": 2,
"createdAt": "2019-05-12 13:18:36",
"updatedAt": "2019-05-12 13:18:39",
"deletedAt": null,
"lession_student": {
"lessionId": 4,
"studentId": 6,
"createdAt": "2019-05-12 13:25:12",
"updatedAt": "2019-05-12 13:25:15",
"deletedAt": null
},
"info": {
"sex": "男",
"id": 6,
"name": "景松",
"age": 23,
"studentId": 6,
"createdAt": "2019-05-12 13:30:07",
"updatedAt": "2019-05-12 13:30:10",
"deletedAt": null
}
}
]
}
]
4. 总结
用时4小时,调试加数据库设置,代码编写,查文档。允许我偷个懒,不想总结了,仔细阅读内容,基本上可以了解Sequelize在联表查询上的基本用法了
2020.3.24补充
1. 模型(表)之间的关联关系
1.1 模型关系概述
数据库中的表之间存在一定的关联关系,表之间的关系基于主/外键进行关联、创建约束等。关系表中的数据分为1对1(1:1)、1对多(1:M)、多对多(N:M)三种关联关系。
在Sequelize中建立关联关系,通过调用模型(源模型)的belongsTo、hasOne、hasMany、belongsToMany方法,再将要建立关系的模型(目标模型)做为参数传入即可。这些方法会按以下规则创建关联关系:
hasOne - 与目标模型建立1:1关联关系,关联关系(外键)存在于目标模型中。详见:Model.hasOne()
belongsTo - 与目标模型建立1:1关联关系,关联关系(外键)存在于源模型中。详见:Model.belongsTo()
hasMany - 与目标模型建立1:N关联关系,关联关系(外键)存在于目标模型中。详见:Model.hasMany()
belongsToMany - 与目标模型建立N:M关联关系,会通过sourceId和targetId创建交叉表。详见:Model.belongsToMany()
1.2 定义关系模型
为了能够清楚说明模型关系的定义及关系模型的使用,我们定义如下4个模型对象:
用户(User)-与其它模型存在1:1、1:N、N:M
用户登录信息(UserCheckin)-与User存在1:1关系
用户地址(UserAddress)-与User存在N:1关系
角色(Role)-与User存在N:M关系
这几个模型的E-R结构如下:
2. 关系/关联相关的API
2.1 综合介绍
在Sequelize中创建关联通过调用模型(源)的 belongsTo / hasOne / hasMany / belongsToMany方法完成,并且为这个方法第一个参数提供另一个模型(目标)。各种方法以下规则创建关联:
hasOne - 添加外键到目标模型,并以单数关系混入到源模型
belongsTo - 为当前模型添加外键,并以单数关系混入到源模型
hasMany - 添加外键到目标模型,并以复数关系混入到源模型
belongsToMany - 为连接的表创建N:M关系并以复数关系混入到源模型。会通过sourceId和targetId创建交叉表。
在创建关系时,可以通过as选项指定别名。这在对一模型引用两次,或者对关联模型使用定义之外的名称时非常有用。
User.hasMany(Picture)
User.belongsTo(Picture, { as: 'ProfilePicture', constraints: false })
user.getPictures() // 获取所有图片
user.getProfilePicture() // 仅获取主图
User.findAll({
where: ...,
include: [
{ model: Picture }, // 加载所有图片
{ model: Picture, as: 'ProfilePicture' }, // 加载主图,名称拼写必须与关联关系中命名相同
]
})
要完全控制通过Sequlize 添加的外键列,可以使用foreignKey选项。选项值可以是表示名称的字符串或类似使用sequelize.define进行模型定义时对象。
User.hasMany(Picture, { foreignKey: 'uid' })
这样外键列会使用uid代替默认的userId。
User.hasMany(Picture, {
foreignKey: {
name: 'uid',
allowNull: false
}
})
指定uid列不能为NULL。在大多数情况下,这将覆盖的外键约束,这sequelize自动创建的,这在外键禁用时非常有用。
当匹配关联模型时,可限制只匹配部分模型。这些查询条件与在find/findAll中的使用方式相同。如,只查找'jpg'格式的图片:
user.getPictures({
where: {
format: 'jpg'
}
})
2.2 Model.hasOne() - 拥有一个
Model.hasOne(target, [options])
创建当前模型(源)到目标模型之间的关系,外键会被添加到目标模型中。
名称类型说明
target
Model
[options]
object
[options.hooks=false]
boolean
设置为 true 时,会在关联模型删除时执行 before-/afterDestroy 钩子方法
[options.as]
string
当前模型(源)的别名,单数形式。如果你为一个表创建多次关联,或者不想使用定义模型时使用的名称,那么就应该为模型指定一个别名。
[options.foreignKey]
string | object
目标表中的外键名或相当于定义外键列的对象 (语法参考 Sequelize.define )。使用对象时,应该添加一个name来设置列名。默认的外键命名规为源模型名+源模型主键名
[options.onDelete='SET NULL | CASCADE']
string
如果外允许空则 SET NULL,其它则 CASCADE
[options.onUpdate='CASCADE']
string
[options.constraints=true]
boolean
是否在删除或更新时启用外键约束
2.3 Model.belongsTo() - 属于
Model.belongsTo(target, [options])
创建当前模型(源)到目标模型之间的关系,外键会被添加到源模型中。
名称类型说明
target
Model
[options]
object
[options.hooks=false]
boolean
设置为 true 时,会在关联模型删除时执行 before-/afterDestroy 钩子方法
[options.as]
string
当前模型(源)的别名,单数形式。如果你为一个表创建多次关联,或者不想使用定义模型时使用的名称,那么就应该为模型指定一个别名。
[options.foreignKey]
string | object
目标表中的外键名或相当于定义外键列的对象 (语法参考 Sequelize.define )。使用对象时,应该添加一个name来设置列名。默认的外键命名规为源模型名+源模型主键名
[options.scope]
object
键/值 集合,用于目标的创建和查找操作(sqlite 不支持 N:M)
[options.onDelete='SET NULL | NO ACTION']
string
如果外允许空则 SET NULL,其它则 CASCADE
[options.onUpdate='CASCADE']
string
[options.constraints=true]
boolean
是否在删除或更新时启用外键约束
2.4 Model.hasMany() - 拥有多个
Model.hasMany(target, [options])
创建当前模型(源)到目标模型之间的 1:m 的关系,外键会被添加到目标模型中。
名称类型说明
target
Model
[options]
object
[options.hooks=false]
boolean
设置为 true 时,会在关联模型删除时执行 before-/afterDestroy 钩子方法
[options.as]
string
当前模型(源)的别名,单数形式。如果你为一个表创建多次关联,或者不想使用定义模型时使用的名称,那么就应该为模型指定一个别名。
[options.foreignKey]
string | object
目标表中的外键名或相当于定义外键列的对象 (语法参考 Sequelize.define )。使用对象时,应该添加一个name来设置列名。默认的外键命名规为源模型名+源模型主键名
[options.targetKey]
string
用于关联目标表的字段名。默认为目标表的主键。
[options.onDelete='SET NULL | NO ACTION']
string
如果外允许空则 SET NULL,其它则 CASCADE
[options.onUpdate='CASCADE']
string
[options.constraints=true]
boolean
是否在删除或更新时启用外键约束
2.5 Model.belongsToMany() - 多对多
Model.belongsToMany(target, [options])
创建连接表的 N:M 的关系
User.belongsToMany(Project, { through: 'UserProjects' })
Project.belongsToMany(User, { through: 'UserProjects' })
定义中指定需要through时,sequelize会尝试自动生成名字,但生成的名字并不一定符合逻辑。
你通过自定义属性定义一个模型,它的属性可以用两种方式添加/设置关联。
var UserProjects = sequelize.define('UserProjects', {
started: Sequelize.BOOLEAN
})
User.belongsToMany(Project, { through: UserProjects })
Project.belongsToMany(User, { through: UserProjects })
jan.addProject(homework, { started: false }) // homework 工程还未开始
jan.setProjects([makedinner, doshopping], { started: true}) // shopping和dinner 两种方式都会启动
如果你想设置多个目标实例,但是有不同的属性,这时必须在实例上设置属性:
p1.UserProjects = {
started: true
}
user.setProjects([p1, p2], {started: false})
类似的,使用自定义属性连接表时,这些属性将做为一个对象的名称:
user.getProjects().then(function (projects) {
var p1 = projects[0]
p1.UserProjects.started // Is this project started yet?
})
名称类型说明
target
Model
[options]
object
[options.hooks=false]
boolean
设置为 true 时,会在关联模型删除时执行 before-/afterDestroy 钩子方法
[options.through]
Model | string | object
在N:M 的关联中,用于连接源 和 目标 表的名称
[options.through.model]
Model
用于连接 N:M 关系的模型
[options.through.scope]
object
用于建立关联的键/值集合,并通过模型查找默认值。
[options.through
.unique=true]
boolean
设置为 true时,唯一键会从使用的外键中生成
[options.as]
string
当前模型(源)的别名,单数形式。如果你为一个表创建多次关联,或者不想使用定义模型时使用的名称,那么就应该为模型指定一个别名。
[options.foreignKey]
string | object
目标表中的外键名或相当于定义外键列的对象 (语法参考 Sequelize.define )。使用对象时,应该添加一个name来设置列名。默认的外键命名规为源模型名+源模型主键名
[options.otherKey]
string | object
连接表的外键名称(表示目标模型)或表示其它列的类型定义(见sequelize.define语法)。使用对象时,可以添加一个name 属性以设置目标列,默认为 目标模型名称 + 目标主键的名称
[options.onDelete='SET NULL | NO ACTION']
string
如果外允许空则 SET NULL,其它则 CASCADE
[options.onUpdate='CASCADE']
string
[options.constraints=true]
boolean
是否在删除或更新时启用外键约束