如何通过Mongoose单次查询获取课程关联的章节、课时及资源？

我是Node.js和MongoDB新手，现有Chapter（章节）、Lecture（课时）、Asset（资源）三个集合，课程包含多个章节，每个章节对应多个课时，每个课时关联若干资源。目前已实现通过courseId获取章节列表，需求是在返回的章节中嵌套其关联的课时，且每个课时嵌套对应的关联资源。

现有模型代码 #

Course模型 #

const mongoose = require('mongoose');
var Double = require('@mongoosejs/double');

const courseSchema = new mongoose.Schema({
    title: {
        type: String,
        required: true,
    },
    requirements: {
        type: String,
    },
    
    code: {
        type: String,
        required: true,
    },
    coverPhoto: {
        type: String,
        required: false,
    },
    
    description: {
        type: String
    },

    instructor:{
        type:mongoose.Schema.Types.ObjectId,
        ref:'User',
        required:true
    },

    category:{
        type:mongoose.Schema.Types.ObjectId,
        ref:'Category',
        required:true
    },

    learns: [{
        type: String
    }],
    subCategory:{
        type:mongoose.Schema.Types.ObjectId,
        ref:'SubCategory',
        required:true
    },


    isCoaching: {
        type: Boolean,
        default: false,
    },

    isFree: {
        type: Boolean,
        default: false,
    },

    price: {
        type: Double,
        default: 0,
    },
    rating: {
        type: Double,
        default: 0,
    },
    isPublished: {
        type: Boolean,
        default: false,
    },

    dateCreated: {
        type:Date,
        default:Date.now,
    },
});

exports.Course = mongoose.model('Course', courseSchema);
exports.courseSchema = courseSchema;

Chapter模型 #

const mongoose = require('mongoose');

const chapterSchema = new mongoose.Schema({
    course:{
        type:mongoose.Schema.Types.ObjectId,
        ref:'Course',
        required:true
    },
    title: {
        type: String,
        required: true,
    },
    sort_order: {
        type: Number,
        default: 1,
    },
    is_published: {
        type: Boolean,
        default: true,
    },

});
exports.Chapter = mongoose.model('Chapter', chapterSchema);
exports.chapterSchema = chapterSchema;

Lecture模型 #

const mongoose = require('mongoose');

const lectureSchema = new mongoose.Schema({
    chapter:{
        type:mongoose.Schema.Types.ObjectId,
        ref:'Chapter',
        required:true
    },

    title: {
        type: String,
        required: true,
    },
    sort_order: {
        type: Number,
        default: 1,
    },

    is_published: {
        type: Boolean,
        default: true,
    },


});

exports.Lecture = mongoose.model('Lecture', lectureSchema);
exports.lectureSchema = lectureSchema;

Asset模型 #

const mongoose = require('mongoose');

const assetSchema = new mongoose.Schema({
    lecture:{
        type:mongoose.Schema.Types.ObjectId,
        ref:'Lecture',
        required:true
    },
    title: {
        type: String,
        required:true
    },
    asset_type: {
        type: String
    },
    description: {
        type: String,
        require:true
    },

    file_url: {
        type: String,
        require:true
    },
    page_number: {
        type: Number,
        default:1
    },
    time_estimation: {
        type: String,
        require:true
    },

    is_external: {
        type: Boolean,
        default: false,
    },

    is_published: {
        type: Boolean,
        default: true,
    },


});


exports.Asset = mongoose.model('Asset', assetSchema);
exports.assetSchema = assetSchema;

现有获取章节接口 #

router.get(`/`, async (req, res) => {
    let course_filter = {};
    if (req.query.course) {
        course_filter = {course:req.query.course};
    }
    const chapterList = await Chapter.find(course_filter).populate('lecture').sort('sort_order');
    if (!chapterList) {
        res.status(500).json({ success: false });
    }
    res.send(chapterList);
});

解决方案 #

方式一：手动分步关联（易理解，适合新手） #

通过分步批量查询，再手动组装嵌套数据，逻辑清晰：

router.get(`/`, async (req, res) => {
    let course_filter = {};
    if (req.query.course) {
        course_filter = { course: req.query.course };
    }
    try {
        // 1. 获取指定课程的章节列表，按排序字段排序
        const chapterList = await Chapter.find(course_filter).sort('sort_order');
        
        // 2. 批量获取所有章节对应的课时
        const chapterIds = chapterList.map(chapter => chapter._id);
        const lectureList = await Lecture.find({ chapter: { $in: chapterIds } }).sort('sort_order');
        
        // 3. 批量获取所有课时对应的资源
        const lectureIds = lectureList.map(lecture => lecture._id);
        const assetList = await Asset.find({ lecture: { $in: lectureIds } });
        
        // 4. 组装嵌套数据
        const result = chapterList.map(chapter => {
            // 匹配当前章节的所有课时
            const chapterLectures = lectureList.filter(lecture => lecture.chapter.toString() === chapter._id.toString());
            // 给每个课时匹配对应的资源
            const lecturesWithAssets = chapterLectures.map(lecture => {
                const lectureAssets = assetList.filter(asset => asset.lecture.toString() === lecture._id.toString());
                return {
                    ...lecture.toObject(),
                    assets: lectureAssets
                };
            });
            return {
                ...chapter.toObject(),
                lectures: lecturesWithAssets
            };
        });
        
        res.send(result);
    } catch (error) {
        console.error(error);
        res.status(500).json({ success: false, message: '获取数据失败' });
    }
});

方式二：MongoDB聚合查询（高效，适合大数据量） #

利用MongoDB的聚合管道$lookup实现多集合关联，一次性返回嵌套结构：

router.get(`/`, async (req, res) => {
    let course_filter = {};
    if (req.query.course) {
        course_filter = { course: req.query.course };
    }
    try {
        const result = await Chapter.aggregate([
            // 筛选指定课程的章节
            { $match: course_filter },
            // 关联课时集合，获取章节对应的所有课时
            {
                $lookup: {
                    from: 'lectures', // 注意是MongoDB实际集合名（默认模型名小写复数）
                    localField: '_id',
                    foreignField: 'chapter',
                    as: 'lectures'
                }
            },
            // 对课时按排序字段排序
            {
                $addFields: {
                    lectures: { $sortArray: { input: '$lectures', sortBy: { sort_order: 1 } } }
                }
            },
            // 关联资源集合，获取所有课时对应的资源
            {
                $lookup: {
                    from: 'assets',
                    localField: 'lectures._id',
                    foreignField: 'lecture',
                    as: 'assets'
                }
            },
            // 将资源匹配到对应的课时中
            {
                $addFields: {
                    lectures: {
                        $map: {
                            input: '$lectures',
                            as: 'lecture',
                            in: {
                                $mergeObjects: [
                                    '$$lecture',
                                    {
                                        assets: {
                                            $filter: {
                                                input: '$assets',
                                                cond: { $eq: ['$$this.lecture', '$$lecture._id'] }
                                            }
                                        }
                                    }
                                ]
                            }
                        }
                    }
                }
            },
            // 移除临时的assets字段
            { $project: { assets: 0 } },
            // 按章节排序字段排序
            { $sort: { sort_order: 1 } }
        ]);
        
        res.send(result);
    } catch (error) {
        console.error(error);
        res.status(500).json({ success: false, message: '获取数据失败' });
    }
});

注意事项 #

• 集合名称：$lookup的from参数必须是MongoDB中的实际集合名称，mongoose默认会将模型名转为小写复数（如Lecture对应lectures），如果你的集合名不是默认规则，需要修改为实际名称。
• 错误处理：添加try/catch捕获异步操作的错误，避免服务器无响应。
• 性能优化：分步查询时使用$in批量查询，避免循环单条查询；聚合查询减少了多次数据库请求，性能更优。

内容的提问来源于stack exchange，提问作者Ismail Muhammad