CloudBase 云数据库实现联表+聚合查询
[[toc]]
#
场景说明我们假设数据库内存在两个集合,class
与 student
,存在以下数据:
class(班级信息)
id | cname | teacher |
---|---|---|
1 | 一班 | 王老师 |
2 | 二班 | 徐老师 |
student(学生信息) | sname | class_id | score | | ----- | -------- | ----- | | 宁一 | 1 | 90 | | 张二 | 2 | 100 | | 李二 | 2 | 80 |
现在需要查询徐老师所带的班级里面所有学生的平均成绩。
#
代码示例#
1、lookup 联表查询首先我们需要把 student
内的所有数据,按照 class_id
进行分组,这里我们使用云数据库的 lookup 操作符:
lookup({ from: "student", //要关联的表student localField: "id", //class表中的关联字段 foreignField: "class_id", //student表中关联字段 as: "stu" //定义输出数组的别名}).end();
这个语句会查出来下面的结果,会查出班级的信息以及该班级所对应的所有学生的信息:
{"list": [{ "id":1, "teacher":"王老师", "cname":"一班", "stu":[ { "sname":"宁一", "class_id":1, "score":90 } ] }, { "id":2, "teacher":"徐老师", "cname":"二班", "stu":[ { "class_id":2, "sname":"张二", "score":100 }, { "class_id":2, "sname":"李二", "score":80 } ] }]}
但是我们只需要徐老师所在班级学生的数据,所以需要进一步过滤。
#
2、match 条件匹配.lookup({ from: 'student', localField: 'id', foreignField: 'class_id', as: 'stu'}).match({ teacher:"徐老师"}).end()
现在就只是返回徐老师所在班级的学生数据了,学生数据在 stu 对应的数组里面:
{ "list": [ { "_id": "5e847ab25eb9428600a512352fa6c7c4", "id": 2, "teacher": "徐老师", "cname": "二班", //学生数据 "stu": [ { "_id": "37e26adb5eb945a70084351e57f6d717", "class_id": 2, "sname": "张二", "score": 100 }, { "_id": "5e847ab25eb945cf00a5884204297ed8", "class_id": 2, "sname": "李二", "score": 80 } ] } ]}
接下来我们继续优化代码,直接返回学生的平均分数。
#
3、直接返回学生成绩平均值如果想要在被连接的表格中(本课程中的 student)做聚合操作,就用 pipeline 方法:
.lookup({ from: 'student', pipeline: $.pipeline() .group({ _id: null, score: $.avg('$score') }) .done(), as: 'stu'}).match({ teacher:"徐老师"}).end()
现在输出的数据是这样的:
{ "list": [ { "_id": "5e847ab25eb9428600a512352fa6c7c4", "id": 2, "teacher": "徐老师", "cname": "二班", "stu": [{ "_id": null, "score": 90 }] } ]}
但是现在输出的数据有点复杂,如果只想显示 teacher 和 score 这两个值,我们再进行下面的操作。
#
4、只显示 teacher 和 score 这两个值我们使用 replaceRoot、mergeObjects 和 project 进行最后的处理:
.lookup({ from: 'student', pipeline: $.pipeline() .group({ _id: null, score: $.avg('$score') }) .done(), as: 'stu'}).match({ teacher:"徐老师"}).replaceRoot({ newRoot: $.mergeObjects([$.arrayElemAt(['$stu', 0]), '$$ROOT'])}).project({ _id:0, teacher:1, score:1}).end()
现在输出的数据是这样的:
{ "list": [{ "score": 90, "teacher": "徐老师" }] }