Mongoose 多表（N个表）关联查询概述
需求：文章（article），文章分类（articlecate），用户（user）这三个表之间的关系，一篇文章对应文章分类表中的某个类型，对应着用户表中的某个用户
Mongoose 多表（N个表）关联查询代码实现
首先我们需要创建db.js（连接数据库）的模块
var mOngoose= require(‘mongoose‘);mongoose.connect(‘mongodb://127.0.0.1:27017/eggcms‘,{ useNewUrlParser: true },function(err){ if(err){ console.log(err); return; } console.log(‘数据库连接成功‘)});module.exports = mongoose;
通过上面的需求我们需要建立三个model模块分别是article.js，articlecate.js，user.js
var mOngoose= require(‘./db.js‘);var Schema = mongoose.Schema;var ArticleSchema = new Schema({ title: { type: String, unique: true }, cid: { // 分类id type: Schema.Types.ObjectId }, author_id:{ // 用户的id type: Schema.Types.ObjectId }, author_name:{ type:String }, descripton: String, content: String});module.exports = mongoose.model(‘Article‘,ArticleSchema,‘article‘);
var mOngoose= require(‘./db.js‘);var ArticleCateSchema = new mongoose.Schema({ title : { type: String, unique: true }, descripton: String, addtime: { type: Date }});module.exports = mongoose.model(‘ArticleCate‘,ArticleCateSchema,‘articlecate‘);
var mOngoose= require(‘./db.js‘);var UserSchema = new mongoose.Schema({ username: { type: String, unique: true }, password: String, name: String, age: Number, sex: String, tel: Number, status: { type: Number, default: 1 }});module.exports = mongoose.model(‘User‘,UserSchema,‘user‘);
然后分别对应三个添加数据的模块article_add.js，articlecate_add.js，user_add.js
var ArticleModel = require(‘./model/article.js‘);var article = new ArticleModel();article.title="这是一个国际新闻333333333"article.cid=‘5cde87e21ebf22597c973f1f‘; // 分类idarticle.author_id=‘5cde88f71faa8045e439838b‘; // 用户idarticle.author_name=‘李四‘;article.descripton=‘这是一个国际新闻333333333333 此处省略300字‘;article.content=‘访问美国 这是一个国际新闻333333333‘article.save();
var ArticleCateModel = require(‘./model/articlecate.js‘);// 分类的增加var cate = new ArticleCateModel({ title:‘地方新闻‘, description:‘地方新闻‘})cate.save();
var UserModel = require(‘./model/user.js‘);// 增加用户var user= new UserModel({ username :‘wangwu‘, password:‘qwerqwerqewrq‘, name:‘王五‘, age:21, sex:‘男‘, tel:12345678987})user.save();
当我们添加了一些数据之后再app.js中进行数据库查询的操作
查询文章信息
var ArticleModel = require(‘./model/article.js‘)// 查询文章信息ArticleModel.find({}, (err,docs) => { console.log(docs)})
两个表关联查询
var ArticleModel = require(‘./model/article.js‘);//两个表关联查询ArticleModel.aggregate([ { $lookup: { from: "articlecate", localField: "cid", foreignField: "_id", as: "cate" } }],function(err,docs){ console.log(JSON.stringify(docs))})
将其转成正常的JSON便于查看效果
多表关联查询
查询文章信息 并显示文章的分类 以及文章的作者信息
var ArticleModel = require(‘./model/article.js‘);// 查询文章信息 并显示文章的分类 以及文章的作者信息// 三个表关联查询ArticleModel.aggregate([{ $lookup: { from: "articlecate", localField: "cid", foreignField: "_id", as: "cate" }},{ $lookup: { from: "user", localField: "author_id", foreignField: "_id", as: "user" }}],function(err,docs){ console.log(JSON.stringify(docs));})
将其转成正常的JSON便于查看效果
[{ "_id": "5cde89b2ecdd5e4864bdaa44", "title": "这是一个国内新闻11111111", "cid": "5cde87ce5ac6d7551041c568", "author_id": "5cde8913a0e31553449bdf54", "author_name": "王五", "descripton": "这是一个国内新闻11111111 此处省略300字", "content": "访问美国 这是一个国内新闻11111111", "__v": 0, "cate": [{ "_id": "5cde87ce5ac6d7551041c568", "title": "国内新闻", "__v": 0 }], "user": [{ "_id": "5cde8913a0e31553449bdf54", "status": 1, "username": "wangwu", "password": "qwerqwerqewrq", "name": "王五", "age": 21, "sex": "男", "tel": 12345678987, "__v": 0 }]}, { "_id": "5cde89dc0d116f45fca7f559", "title": "这是一个国际新闻222222222222", "cid": "5cde87e21ebf22597c973f1f", "author_id": "5cde8913a0e31553449bdf54", "author_name": "王五", "descripton": "这是一个国际新闻222222222222 此处省略300字", "content": "访问美国 这是一个国际新闻222222222222", "__v": 0, "cate": [{ "_id": "5cde87e21ebf22597c973f1f", "title": "国际新闻", "__v": 0 }], "user": [{ "_id": "5cde8913a0e31553449bdf54", "status": 1, "username": "wangwu", "password": "qwerqwerqewrq", "name": "王五", "age": 21, "sex": "男", "tel": 12345678987, "__v": 0 }]}, { "_id": "5cde8a078a1ab250f4dbb8e0", "title": "这是一个国际新闻333333333", "cid": "5cde87e21ebf22597c973f1f", "author_id": "5cde88f71faa8045e439838b", "author_name": "李四", "descripton": "这是一个国际新闻333333333333 此处省略300字", "content": "访问美国 这是一个国际新闻333333333", "__v": 0, "cate": [{ "_id": "5cde87e21ebf22597c973f1f", "title": "国际新闻", "__v": 0 }], "user": [{ "_id": "5cde88f71faa8045e439838b", "status": 1, "username": "lisi", "password": "13214lkisisgfdsgsdsg", "name": "李四", "age": 20, "sex": "男", "tel": 124212142151, "__v": 0 }]}]
【本文由：阿里云代理 http://www.56aliyun.com欢迎留下您的宝贵建议】