Mongoose关联查询问题：筛选指定分类及红色库存的商品

我拥有三个Mongoose模型：Product、Category和Stock。Product模型的categories数组用于存储Category的ID，stocks数组用于存储Stock的ID。需要查询同时满足以下条件的商品：

• 商品关联的Category ID等于req.categoryId（由converttoslugtocategoryId中间件从URL路径cell-phone转换而来）
• 商品关联的Stock中存在颜色为req.query.color（示例值为red）的记录

接口地址：http://localhost:3000/api/products/cell-phone?color=red

模型定义 #

ProductModel

const mongoose = require("mongoose");
const Stock = require("./Stock");
const ProductModel = new mongoose.Schema({
    name: {
        type: String,
        required: [true, "ürün ad alanı boş bırakılamaz"],
    },
    description: {
        type: String,
        required: [true, "ürün açıklama alanı boş bırakılamaz"]
    },
    slug: String,
    createdAt: {
        type: Date,
        default: Date.now
    },
    properties: [
       String
    ],
    image: {
        type: String,
        default: "default.png"
    },
    images: {
        type: [String],

    },
    size: {
        type: String,
    },
    color: {
        type: String
    },
    price: {
        type: Number,
        default: 0
    },
    supplier: {
        type: mongoose.Schema.ObjectId,
        ref: "Supplier"
    },
    categories:[
        {
            type: mongoose.Schema.ObjectId,
            ref: "Category" 
        }
    ],
    rating: Number,
    comments: [
        {
            type: mongoose.Schema.ObjectId,
            ref: "Comment"
        }
    ],
    stocks: [
        {
            type: mongoose.Schema.ObjectId,
            ref: "Stock"
        }
    ],
    visible: {
        type: Boolean,
        default: true
    }
})

module.exports = mongoose.model("Product", ProductModel)

CategoryModel

const mongoose = require("mongoose");
const sluqify = require("slugify")
const CategoryModel = new mongoose.Schema({
   
    parentId: {
        type: String,
        default: null
    },
    name: {
        type:String
    },
    children: [
        {
            type: mongoose.Schema.ObjectId,
            ref: "Category"
        }
    ],
    properties:[
        {
            type: mongoose.Schema.ObjectId,
            ref:"PropertyOfCategory"
        }
    ],
    
    slug:String
})

module.exports = mongoose.model("Category", CategoryModel)

StockModel

const mongoose = require("mongoose");
const fs = require("fs")
const Product = require("./Product");

const StockModel = new mongoose.Schema({
    product: {
        type: mongoose.Schema.ObjectId,
        ref:"Product"
    },
    size: {
        type: String
    },
    color: {
        type: String
    },
    piece: {
        type:Number, 
        default: 0
    },
    price: {
        type: Number,
        default:0
    },
    base:{
        type:Boolean,
        default:false
    },
    status: {
        type: Boolean,
        default:true
    },
    image:{
        type: String,

    },
    images: {
        type:[String]
    }   
})

StockModel.methods.updateProductBaseStock =  function(productId){
    const product =  Product.findById(productId);
    product.stocks[0].base = true;
        product.size = this.size;
        product.color = this.color;
        product.price = this.price
        product.save();
}
StockModel.methods.removeOtherPictures = function(stockId, picNames){
    fs.readdir(process.cwd()+"/public/uploads", (err, files)=> {
        if(err){
            console.log(`${process.cwd()}/public/uploads yolu bulunamadı: ${err}`)
        }else{
            if(Array.isArray(picNames)){
                picNames.map(picName => {
                    if(files.includes(picName)){
                        fs.unlink(process.cwd()+`/public/uploads/${picName}`, function(err){
                            if(err) console.log("dosya silme işlemi sırasında hatalarla karşılaşıldı: "+ err)
                        })
                    }
                })
            }
        }
    })
}

module.exports = mongoose.model("Stock", StockModel)

尝试过的无效查询 #

const products = await Product.find({categories: req.categoryId, 'stocks.color': req.query.color}}).populate('stocks')

const products = await Product.find({categories: req.categoryId}).populate({path:"stocks", match:{'color': {'$eq': req.query.color}}}).where({"stocks": {"$ne": []});

• 第一个查询：'stocks.color' 写法错误。Product模型的stocks是ObjectId数组，并非嵌入文档，MongoDB无法直接通过该路径关联Stock模型的color字段进行查询。
• 第二个查询：populate的match仅会过滤返回的Stock数据，但不会过滤Product本身。即使某个Product没有符合颜色条件的Stock，只要它属于目标分类，仍会被返回，只是其stocks数组为空。后续的where({"stocks": {"$ne": []}})也无效，因为这里的stocks是原Product文档中的ID数组，而非populate后的结果。

方案1：聚合管道查询（推荐） #

通过$lookup关联Stock集合，一步完成分类匹配和库存颜色过滤：

const mongoose = require("mongoose");

const products = await Product.aggregate([
  // 匹配属于目标分类的商品
  {
    $match: {
      categories: mongoose.Types.ObjectId(req.categoryId)
    }
  },
  // 关联Stock集合，获取商品对应的所有库存详情
  {
    $lookup: {
      from: "stocks", // Stock模型对应的集合名（Mongoose默认转复数）
      localField: "stocks",
      foreignField: "_id",
      as: "stockDetails"
    }
  },
  // 过滤出存在符合颜色条件库存的商品
  {
    $match: {
      "stockDetails.color": req.query.color
    }
  },
  // 可选：自定义返回字段，保留原Product字段并添加库存详情
  {
    $project: {
      name: 1,
      description: 1,
      price: 1,
      // 按需添加其他需要返回的字段
      stockDetails: 1
    }
  }
]);

方案2：分步查询 #

先找到符合颜色条件的Stock，再关联查询对应的Product并过滤分类：

const mongoose = require("mongoose");

// 1. 查询所有颜色匹配的Stock，提取对应的Product ID
const targetStocks = await Stock.find({ color: req.query.color }).select("product");
const productIds = targetStocks.map(stock => stock.product);

// 2. 查询属于目标分类且ID在productIds中的商品，可选仅返回符合条件的Stock
const products = await Product.find({
  categories: mongoose.Types.ObjectId(req.categoryId),
  _id: { $in: productIds }
}).populate({
  path: "stocks",
  match: { color: req.query.color } // 仅返回符合颜色条件的库存
});

注意事项 #

• 确保req.categoryId被转换为mongoose.Types.ObjectId类型，如果中间件返回的是字符串，需手动转换。
• 方案1适合数据量较小的场景，逻辑紧凑；方案2适合数据量较大的场景，拆分查询可提升性能。

内容的提问来源于stack exchange，提问作者ali eren gönülçelen