9.mongoose的多表关联查询

需求

实现多表查询

解决方案

使用aggregate中的$lookup做多表关联。

注：这里能看明白就不用往下看了，重点就是多个lookup连写

// 查询文章信息，并显示文章分类和作者信息
AriticleModel.aggregate([
    {
        $lookup: {
            from: 'articlecate',
            localField: 'cid',
            foreignField: '_id',
            as: 'articlecate_info'
        }
    },
    {
        $lookup: {
            from: 'users',
            localField: 'author_id',
            foreignField: '_id',
            as: 'users_info'
        }
    },
    {
        $match: { title: '国内新闻1' }
    }
], (err, doc) => {
    if(err){ console.log('err: ', err); return }
    console.log('doc', JSON.stringify(doc));
})

完整案例

1.先创建3个model

articlecate.js

const mongoose = require('./db')

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

module.exports = mongoose.model('Articlecate', ArticlecateSchema, 'articlecate')

article.js

const { Schema } = require('mongoose')
const mongoose = require('./db')

const ArticleSchema = mongoose.Schema({
    title: { type: String, unique: true },
    cid: { type: Schema.Types.ObjectId }, // 分类id
    author_id: { type: Schema.Types.ObjectId }, //用户id
    author_name: { type: String },
    description: String,
    content: String,
})

module.exports = mongoose.model('Article', ArticleSchema, 'article')
// 注：Schema.Types.ObjectId会自动将字符串格式化

user.js

const mongoose = require('./db');
const UserSchema = 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, 'users')

2. 增加数据

add.js

const ArticlecateModel = require('./model/articlecate')
const UserModel = require('./model/user')
const AriticleModel = require('./model/article')

// 增加分类
const articlecate = new ArticlecateModel({
    title: '国内新闻',
    description: '国内新闻',
})
// articlecate.save()

// 增加用户
const user = new UserModel({
    username: 'lisi',
    password: '123456',
    name: '李四',
    age: 20,
    sex: '男',
    tel: 110,  
})
// user.save()

// 增加文章
const article = new AriticleModel()
article.title = '国内新闻2';
article.cid = '602be7f644449e1913f9b321';
article.author_id = '602be97ec3665b195436dd3d';
article.author_name = '李四';
article.description = '新闻新闻新闻xx';
article.content = '新闻新闻新闻xxxxx';

article.save()

运行 node add.js 来增加数据

3.多表关联查询

const AriticleModel = require('./model/article')

// 查询文章信息，并显示文章分类和作者信息
AriticleModel.aggregate([
    {
        $lookup: {
            from: 'articlecate',
            localField: 'cid',
            foreignField: '_id',
            as: 'articlecate_info'
        }
    },
    {
        $lookup: {
            from: 'users',
            localField: 'author_id',
            foreignField: '_id',
            as: 'users_info'
        }
    },
    {
        $match: { title: '国内新闻1' }
    }
], (err, doc) => {
    if(err){ console.log('err: ', err); return }
    console.log('doc', JSON.stringify(doc));
})