Mongoose 多表（N个表）关联查询aggregate

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';   // 分类id
article.author_id='5cde88f71faa8045e439838b'; // 用户id
article.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
    }]
}]