i have a collection of category and a collection of products, each product has a categoryId field that has an id of a perticular category. I want to know how many products are there for each category.

const categorySchema = mongoose.Schema({
  name: String,
});

const productSchema = mongoose.Schema({
  name: { type: String, required: true },
  categoryId: [{ type: mongoose.Schema.Types.ObjectId, ref: 'Category' }],
});


categoryCollection = [
  { name: "cat1", _id: id1 },
  { name: "cat2", _id: id2 },
  { name: "cat3", _id: id3 },
  { name: "cat4", _id: id4 },
  { name: "cat5", _id: id5 },
]

productsCollection = [
  {
    name: "product1",
    categoryId: [id1, id2]
  },
  {
    name: "product2",
    categoryId: [id1, id3]
  }
]

i want the result like this result = [{cat1:2,cat2:1,cat3:1,cat4:0,cat5:0}]

i was trying to do it like first getting all the ids from the category collection and matching them with the categoryId field but it returns only the matched products and i am unable to get the count for each product

ids=[id1,id2,id3,id4,id5]

Prodcut.find({categoryId:{$in:ids}})

CodePudding user response：

try using lookup in mongo aggregation.

Category.aggregate([
  {
  _id: {
     $in:[
        ids //make sure to add it in objectid formats
     ]
    }
  },
  {
    '$lookup': {
      'from': 'product', 
      'localField': '_id', 
      'foreignField': 'categoryId', 
      'as': 'products'
    }
  }, {
    '$addFields': {
      'total': {
        '$size': '$products'
      }
    }
  }, {
    '$project': {
      '_id': 1, 
      'name': 1, 
      'total': 1
    }
  }
])

this aggregation will generate response like this

[{
   "name":"cat1", 
   "total":2 
},
{
   "name":"cat2", 
   "total":1 
},
{
   "name":"cat3", 
   "total":1 
},
{
   "name":"cat4", 
   "total":0
},
{
   "name":"cat5", 
   "total":0
}]

• if you want to add any match in category field then you can add it before the lookup stage.

• if you need match in lookup then add after lookup stage

CodePudding user response：

So you have the right idea. Once you have all the products, you can simply add another function to parse through the returned documents, and count how many products there are.

const grab_products = async(ids= [id1, id2]) =>{
      const all_products = await Prodcut.find({categoryId:{$in:ids}})
      //create a map to separate and categorize all products into their 
       // respective categories
       const id_map = {}
      for(let id of ids){
         id_map[id] = []
      }
      //go through each product and insert them into the map with their category
      for(let product of all_products){
         const category = id_map[product.categoryId]
         category.push(product)
      }
      //Now ids_map hold the products categorized, and you can read 
      //each key, and grab the length for every respective category

      //this is the number of products in category id1
      const products_count_id1 = ids_map[id1].length
      return products_count_id1
}