如何在Mongoose中通过父级关联获取子数据并转换指定SQL查询

First, let's break down what your original SQL is doing:

SELECT * FROM users WHERE teacher_id in (SELECT id from users where parent_id = 1)

This logic first fetches all user IDs where parent_id = 1, then retrieves every user whose teacher_id matches any of those IDs. Let's convert this to Mongoose, with a focus on efficiency to get the exact result set you need.

Assumptions #

Let's assume your Mongoose User model looks something like this (adjust fields to match your actual schema):

const mongoose = require('mongoose');

const userSchema = new mongoose.Schema({
  teacher_id: mongoose.Schema.Types.ObjectId,
  parent_id: mongoose.Schema.Types.ObjectId,
  // Add your other user fields here (name, email, role, etc.)
});

const User = mongoose.model('User', userSchema);

Recommended Approach: Aggregation Pipeline (Single Database Trip) #

For optimal performance, use an aggregation pipeline—it runs the entire logic in one database operation, eliminating unnecessary network round-trips. Here's how to implement it:

const getTargetUsers = async () => {
  const aggregationResult = await User.aggregate([
    // Step 1: Filter users where parent_id = 1 and collect their IDs
    { $match: { parent_id: 1 } },
    {
      $group: {
        _id: null,
        parentUserIds: { $addToSet: '$_id' }
      }
    },
    // Step 2: Look up all users whose teacher_id is in the collected ID list
    {
      $lookup: {
        from: 'users', // Ensure this matches your actual MongoDB collection name (usually pluralized model name)
        localField: 'parentUserIds',
        foreignField: 'teacher_id',
        as: 'targetUsers'
      }
    },
    // Step 3: Clean up output to return only the target users array
    {
      $project: {
        _id: 0,
        targetUsers: 1
      }
    }
  ]);

  // Return the final user list (fallback to empty array if no matches)
  return aggregationResult[0]?.targetUsers || [];
};

// Usage example
const targetUsers = await getTargetUsers();
console.log(targetUsers);

Alternative: Two-Step Query (Simpler for Small Datasets) #

If you're working with a small dataset and prefer more straightforward code, you can split the query into two steps. Note this has extra network overhead compared to the aggregation approach:

const getTargetUsers = async () => {
  // First fetch all parent user IDs where parent_id = 1
  const parentUsers = await User.find({ parent_id: 1 }, '_id');
  const parentIds = parentUsers.map(user => user._id);

  // Then retrieve users with teacher_id in that ID list
  return await User.find({ teacher_id: { $in: parentIds } });
};

// Usage example
const targetUsers = await getTargetUsers();
console.log(targetUsers);

Critical Performance Optimization #

To make both query types run significantly faster, add indexes on the parent_id and teacher_id fields. This lets MongoDB quickly locate matching documents without scanning the entire collection:

// Add these to your user schema before creating the model
userSchema.index({ parent_id: 1 });
userSchema.index({ teacher_id: 1 });

Both approaches will return the exact same document set as your original SQL query, matching the result format shown in your screenshot.

内容的提问来源于stack exchange，提问作者Dipesh Patel