三张表连表查询实例

• app.js

const UserModel = require("./model/user");
const ArticleModel = require("./model/article");
const ArticlecateModel = require("./model/articlecate");

// // 用户新增
// const user = new UserModel({
//   username: "JonSnow1",
//   password: "123456",
//   name: "雪诺1",
//   age: 22,
//   sex: "女",
//   tel: "17777777777"
// });
//
// user.save(err => {
//   if (err) {
//     console.log(err);
//     return;
//   }
//   console.log("user新增成功");
// });

// // 分类的增加
// const articlecate = new ArticlecateModel({
//   title: "信阳新闻",
//   descripton: "信阳新闻"
// });
//
// articlecate.save(err => {
//   if (err) {
//     console.log(err);
//     return;
//   }
//   console.log("user新增成功");
// });

// // 文章新增
// const article = new ArticleModel({
//   title: "河南新闻2",
//   cid: "5dea007bcce50d21fc260efd",
//   author_id: "5dea028ad55add1bd0d2aff7",
//   author_name: "雪诺",
//   descripton: "河南新闻：稀土的股票一直在涨，好后悔没有买啊12121",
//   content: "河南新闻：稀土的股票一直在涨，好后悔没有买啊12121121"
// });
//
// article.save(err => {
//   if (err) {
//     console.log(err);
//     return;
//   }
//   console.log("article新增成功");
// });

• model/user.js

const mongoose = require("./db");

const UserSchema = mongoose.Schema({
  username: {
    type: String,
    unique: true
  },
  password: String,
  name: String,
  age: Number,
  sex: String,
  tel: String,
  status: {
    type: Number,
    default: 1
  }
});

const UserModel = mongoose.model("User", UserSchema, "user");

module.exports = UserModel;

• model/article.js（使用$lookup进行查询）

const mongoose = require("./db");
const Schema = mongoose.Schema;

const ArticleSchema = Schema({
  title: {
    type: String,
    unique: true
  },
  // 分类id
  cid: {
    type: Schema.Types.ObjectId
  },
  // 用户id
  author_id: {
    type: Schema.Types.ObjectId
  },
  author_name: {
    type: String,
    default: "JonSnow"
  },
  descripton: String,
  order: {
    type: Number,
    default: 100
  },
  content: String
});

const ArticleModel = mongoose.model("Article", ArticleSchema, "article");

module.exports = ArticleModel;

• model/article.js（使用 populate 实现关联查询）

const mongoose = require("./db");
const Schema = mongoose.Schema;

const ArticleSchema = Schema({
  title: {
    type: String,
    unique: true
  },
  // 分类id
  cid: {
    type: Schema.Types.ObjectId,
    ref: "Articlecate"
  },
  // 用户id
  author_id: {
    type: Schema.Types.ObjectId,
    ref: "User"
  },
  author_name: {
    type: String,
    default: "JonSnow"
  },
  descripton: String,
  order: {
    type: Number,
    default: 100
  },
  content: String
});

const ArticleModel = mongoose.model("Article", ArticleSchema, "article");

module.exports = ArticleModel;

• model/articlecate.js

const mongoose = require("./db");

const ArticlecateSchema = mongoose.Schema({
  title: {
    type: String,
    unique: true
  },
  descripton: String,
  addtime: {
    type: Date
  }
});

const ArticlecateModel = mongoose.model(
  "Articlecate",
  ArticlecateSchema,
  "articlecate"
);

module.exports = ArticlecateModel;

先按照上面的流程加一波数据

• $lookup查询

const ArticleModel = require("./model/article");

ArticleModel.aggregate(
  [
    {
      $lookup: {
        from: "user",
        localField: "author_id",
        foreignField: "_id",
        as: "user"
      }
    },
    {
      $lookup: {
        from: "articlecate",
        localField: "cid",
        foreignField: "_id",
        as: "articlecate"
      }
    }
  ],
  (err, docs) => {
    if (err) {
      console.log(err);
      return;
    }
    console.log(JSON.stringify(docs, null, 2));
  }
);

• 使用 populate 实现关联查询（注意这里要关联的表都要引入进来）

const ArticleModel = require("./model/article");
const ArticlecateModel = require("./model/articlecate");
const UserModel = require("./model/user");

ArticleModel.find({})
  .populate("author_id")
  .populate("cid")
  .exec((err, docs) => {
    if (err) {
      console.log(err);
      return;
    }
    console.log(JSON.stringify(docs, null, 2));
  });