What I need see all products but category._id = req.categoryId and stocks.color == "red"

Question

I have 3 mongoose model. They are Product, Category and Stock. Product model has got two array. These categories and Stocks. They are include Category and Stock ids. What I need see all products but category._id = req.categoryId and stocks.color == "red".

my endpoint is http://localhost:3000/api/products/cell-phone?color=red and before works product controller converttoslugtocategoryId middleware is run and it's return cell-phone slug's categoryId(req.categoryId).

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)

Try this

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

and this

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

but not work.

Answer 1

Unfortunately, as per the docs the Mongoose populatemethod can't filter out parent documents based on a match condition in child documents.

Thankfully you can use aggregation like so:

const products = await Product.aggregate([
   // Find all Products that match your req.categoryId
   {$match: {'categories' : new mongoose.Types.ObjectId(req.categoryId)}},
   // Unwind the stocks array for each match so that you have 1 Product per stock
   // This will create duplicate products temporarily but we'll do another match to remove them
   {$unwind:"$stocks"},
   // Populate the stocks with documents instead of ObjetIds so we can search inside them
   { "$lookup": {
      "from": "stocks",
      "foreignField": "_id",
      "localField": "stocks",
      "as": "stocks"
   }},
   // Now match only the Products that have color == req.query.color
   {$match: {'stocks.color' : req.query.color}},
   ]).exec();