How to lookup subquery documents with another collection moongoose

Question

I need to join user collections two times as same user will post product and purchased user details as sub document.

Product Schema:

{
    product_title: String,
    product_desc: String,
    Product_purchased:[
        {
            userid: mongoose.Schema.ObjectId,
            purchased_date: Date
        }]
    posteduserId: mongoose.Schema.ObjectId
}

Users Schema:

{
    _id: userId,
    name: String,
    Pic: String
}

Example document:

[
    {
        "product_title":"title",
        "product_desc":"desc",
        "Product_purchased":[
            {
                "userid":"5d4hvh7duc7c7c8d9scbe",
                "name":"name",
                "Pic":"url",
                "purchased_date":"Date"
            },
            {
                "userid":"5d4hvh7duc7c7c8d9scbe",
                "name":"name",
                "Pic":"url",
                "puchased_date":"Date"
            }
        ],
        "posteduserId": "5d4hvh7duc7c7c8d9scbe",
        "userid": "5d4hvh7duc7c7c8d9scbe",
        "name": "name",
        "pic": "url",
    }
]

Join same user table with posted userid and subarray of purchased userIds.

Please help me how to crack this one, Thanks in advance.

Answer 1

First you need to fix your Product schema like this to include the ref field:

const mongoose = require("mongoose");

const ProductSchema = new mongoose.Schema({
  product_title: String,
  product_desc: String,
  Product_purchased: [
    {
      userid: {
        type: mongoose.Schema.ObjectId,
        ref: "User"
      },
      purchased_date: Date
    }
  ],
  posteduserId: {
    type: mongoose.Schema.ObjectId,
    ref: "User"
  }
});

module.exports = mongoose.model("Product", ProductSchema);

I setup user model like this:

const mongoose = require("mongoose");

const UserSchema = new mongoose.Schema({
  name: String,
  Pic: String
});

module.exports = mongoose.model("User", UserSchema);

The ref User value must match the model name User in the user model.

Then you need to populate two times like this:

router.get("/products", async (req, res) => {
  const result = await Product.find({})
    .populate("Product_purchased.userid")
    .populate("posteduserId");
  res.send(result);
});

Let's say you have this 3 users:

{
    "_id": "5e133deb71e32b6a68478ab4",
    "name": "user1 name",
    "Pic": "user1 pic",
    "__v": 0
},
{
    "_id": "5e133df871e32b6a68478ab5",
    "name": "user2 name",
    "Pic": "user2 pic",
    "__v": 0
},
{
    "_id": "5e133e0271e32b6a68478ab6",
    "name": "user3 name",
    "Pic": "user3 pic",
    "__v": 0
}

And this product:

{
    "_id": "5e133e9271e32b6a68478ab8",
    "product_title": "product1 title",
    "product_desc": "product1 description",
    "Product_purchased": [
        {
            "purchased_date": "2020-01-06T14:02:14.029Z",
            "_id": "5e133e9271e32b6a68478aba",
            "userid": "5e133df871e32b6a68478ab5"
        },
        {
            "purchased_date": "2020-01-06T14:02:14.029Z",
            "_id": "5e133e9271e32b6a68478ab9",
            "userid": "5e133e0271e32b6a68478ab6"
        }
    ],
    "posteduserId": "5e133deb71e32b6a68478ab4",
    "__v": 0
}

The result will be:

[
    {
        "_id": "5e133e9271e32b6a68478ab8",
        "product_title": "product1 title",
        "product_desc": "product1 description",
        "Product_purchased": [
            {
                "purchased_date": "2020-01-06T14:02:14.029Z",
                "_id": "5e133e9271e32b6a68478aba",
                "userid": {
                    "_id": "5e133df871e32b6a68478ab5",
                    "name": "user2 name",
                    "Pic": "user2 pic",
                    "__v": 0
                }
            },
            {
                "purchased_date": "2020-01-06T14:02:14.029Z",
                "_id": "5e133e9271e32b6a68478ab9",
                "userid": {
                    "_id": "5e133e0271e32b6a68478ab6",
                    "name": "user3 name",
                    "Pic": "user3 pic",
                    "__v": 0
                }
            }
        ],
        "posteduserId": {
            "_id": "5e133deb71e32b6a68478ab4",
            "name": "user1 name",
            "Pic": "user1 pic",
            "__v": 0
        },
        "__v": 0
    }
]