Filtering multiple nested ObjectId's

Working with Data
, , ,
#1

Product Model:

{
  ...
  department: {
    type: mongoose.Schema.Types.ObjectId,
    ref: "Department",
    required: true,
  },
  category: {
    type: mongoose.Schema.Types.ObjectId,
    ref: "Category",
    required: true,
  },
  variations: [
    {
      type: mongoose.Schema.Types.ObjectId,
      ref: "Variation",
    },
  ],
  ...
}

Variation Model:

{
  color: {
    type: mongoose.Schema.Types.ObjectId,
    ref: "Color",
    required: true,
  },
  size: {
    type: mongoose.Schema.Types.ObjectId,
    ref: "Size",
    required: true,
  },
  ...
}

I tried to find() using mongodb with both of the filters below:

{ variations: { '$elemMatch': { color: '639b75a799cf2ed9eb2802ec' } } }
{ "variations.color": "639b75a799cf2ed9eb2802ec" }

neither worked. any idea?

#2

Because your types are ObjectId and

is a string. Types and values must match.

#3

mongoose.Types.ObjectId("639b75a799cf2ed9eb2802ec") didn’t work either

#4

Please share sample documents from both collection.

Share exactly the code that you have tried.

#5

Product Document:

{
  "_id": {
    "$oid": "63a48b89cd827b16f31ac9b2"
  },
  "name": "Ali's Boxers",
  "description": "Boxers made specifically for ALi",
  "department": {
    "$oid": "6398fbf11fc0f2835e898aa8"
  },
  "category": {
    "$oid": "6398fbfd1fc0f2835e898aae"
  },
  "variations": [
    {
      "$oid": "63a48b89cd827b16f31ac9b9"
    },
    {
      "$oid": "63a48b8acd827b16f31ac9c1"
    }
  ],
  "price": 50,
  "discount": 0,
  "identifier": "A",
  "createdAt": {
    "$date": {
      "$numberLong": "1671728010297"
    }
  },
  "updatedAt": {
    "$date": {
      "$numberLong": "1671728010297"
    }
  },
  "__v": 0
}

Variation with id 63a48b89cd827b16f31ac9b9 document:

{
  "_id": {
    "$oid": "63a48b89cd827b16f31ac9b9"
  },
  "color": {
    "$oid": "6398fbdb1fc0f2835e898a91"
  },
  "size": {
    "$oid": "6398fbe61fc0f2835e898aa1"
  },
  "stock": [
    {
      "$oid": "63a48b89cd827b16f31ac9b5"
    }
  ],
  "images": {
    "$oid": "63a48b89cd827b16f31ac9b3"
  },
  "createdAt": {
    "$date": {
      "$numberLong": "1671728009397"
    }
  },
  "updatedAt": {
    "$date": {
      "$numberLong": "1671728009397"
    }
  },
  "__v": 0
}

Color with id 6398fbdb1fc0f2835e898a91 document:

{
  "_id": {
    "$oid": "6398fbdb1fc0f2835e898a91"
  },
  "name": "red",
  "code": "#d44a4a",
  "skuCode": "001",
  "createdAt": {
    "$date": {
      "$numberLong": "1670970331140"
    }
  },
  "updatedAt": {
    "$date": {
      "$numberLong": "1671006396023"
    }
  },
  "__v": 0
}

My API:

exports.getAll = async (req, res) => {
  try {
    const { offset, limit } = req.query;
    const offsetInt = parseInt(offset) || 0;
    const limitInt = parseInt(limit) || 15;

    const queryObj = buildProductQuery(req.query);

    const products = await Product.find(queryObj)
      .limit(limitInt)
      .skip(offsetInt)
      .populate(populateQuery);

    const totalProductsCount = await Product.countDocuments(queryObj);
    const totalPages = Math.ceil(totalProductsCount / limitInt);
    const currentPage = Math.ceil(offsetInt / limitInt) + 1;

    return Response.success(res, {
      products,
      pagination: {
        total: totalProductsCount,
        pages: totalPages,
        current: currentPage,
      },
    });
  } catch (err) {
    return Response.serverError(res, err.message);
  }
};

const buildProductQuery = (query) => {
  const { department, category, color, size } = query;

  const queryObj = {
    variations: { $ne: [] },

    ...(department && { department }),
    ...(category && { category }),

    // I tried 2 ways of filtering, you can uncomment the below 2 lines and comment the next block to switch between the 2 (neither worked)
    // I have also tried casting to `mongoose.Types.ObjectId` on both options.

    // ...(color && { "variations.color": color }),
    // ...(size && { "variations.size": size }),

    ...((color || size) && {
      variations: {
        $elemMatch: {
          ...(color && { color: mongoose.Types.ObjectId(color) }),
          ...(size && { size: mongoose.Types.ObjectId(size) }),
        },
      },
    }),
  };

  console.log(queryObj);
  return queryObj;
};
#6

I have just realized that you are using mongoose. I guess I do not really look at the post tags.

Sorry, but I do not know how mongoose work. Hopefully someone with knowledge in this area will pick up.

#7

Hi @Emile_Ibrahim , There is no problem with how you convert ObjectId. The problem is probably about how you use them throughout your document.

It is possible documents are not populated. can you give output for Product.findOne({})?

also, what is populateQuery and the result for Product.findOne({}).populate(populateQuery)?

#8

Hello sorry for the late response. Happy Holidays!

My populate query is:

const populateQuery = [
  {
    path: "department",
    model: Department,
  },
  {
    path: "category",
    model: Category,
  },
  {
    path: "variations",
    model: Variation,
    populate: [
      {
        path: "color",
        model: Color,
      },
      {
        path: "size",
        model: Size,
      },
      {
        path: "stock",
        model: Stock,
        populate: [
          {
            path: "materials.material",
            model: Material,
          },
          {
            path: "labourCost",
            model: LabourCost,
          },
        ],
      },
      {
        path: "images",
        model: ProductImages,
        populate: [
          {
            path: "images",
            model: "Image",
          },
        ],
      },
    ],
  },
];

Product.findOne({}) is:

{
   "_id":"63a5bcc57433d072405cde86",
   "name":"Ali's Boxers",
   "description":"Boxers made specifically for ALi",
   "department":"6398fbf11fc0f2835e898aa8",
   "category":"6398fbfd1fc0f2835e898aae",
   "variations":[
      "63a5bcc57433d072405cde8d",
      "63a5bcc67433d072405cde95"
   ],
   "price":50,
   "discount":0,
   "identifier":"A",
   "createdAt":"2022-12-23T14:35:50.556Z",
   "updatedAt":"2022-12-23T14:35:50.556Z",
   "__v":0
}

Product.findOne({}).populate(populateQuery) is:

{
   "_id":"63a5bcc57433d072405cde86",
   "name":"Ali's Boxers",
   "description":"Boxers made specifically for ALi",
   "department":{
      "_id":"6398fbf11fc0f2835e898aa8",
      "name":"male",
      "description":"male",
      "skuCode":"M",
      "createdAt":"2022-12-13T22:25:53.549Z",
      "updatedAt":"2022-12-13T22:25:53.549Z",
      "__v":0
   },
   "category":{
      "_id":"6398fbfd1fc0f2835e898aae",
      "name":"main",
      "title":"main",
      "description":"main",
      "skuCode":"M",
      "parent":null,
      "createdAt":"2022-12-13T22:26:05.996Z",
      "updatedAt":"2022-12-13T22:26:05.996Z",
      "__v":0
   },
   "variations":[
      {
         "_id":"63a5bcc57433d072405cde8d",
         "color":{
            "_id":"6398fbdb1fc0f2835e898a91",
            "name":"red",
            "code":"#d44a4a",
            "skuCode":"001",
            "createdAt":"2022-12-13T22:25:31.140Z",
            "updatedAt":"2022-12-14T08:26:36.023Z",
            "__v":0
         },
         "size":{
            "_id":"6398fbe61fc0f2835e898aa1",
            "name":"small",
            "skuCode":"Y",
            "createdAt":"2022-12-13T22:25:42.795Z",
            "updatedAt":"2022-12-13T22:25:42.795Z",
            "__v":0
         },
         "stock":[
            {
               "_id":"63a5bcc57433d072405cde89",
               "available":2,
               "reserved":0,
               "store":0,
               "labourCost":{
                  "_id":"63a1deb1d5890e2103410bf4",
                  "title":"Ali Boxers Maker",
                  "description":"Ali needs his boxers hand made",
                  "amount":20,
                  "createdAt":"2022-12-20T16:11:29.690Z",
                  "updatedAt":"2022-12-22T17:06:37.667Z",
                  "__v":0
               },
               "materials":[
                  {
                     "amount":1,
                     "material":null,
                     "_id":"63a5bcc57433d072405cde8a"
                  },
                  {
                     "amount":1,
                     "material":{
                        "_id":"639a251596ae4b5c1af788c0",
                        "name":"mamam",
                        "description":"2121",
                        "stock":121195,
                        "supplier":"test",
                        "costPerUnit":1,
                        "fabric":"polyester",
                        "createdAt":"2022-12-14T19:33:41.047Z",
                        "updatedAt":"2022-12-25T01:40:56.383Z",
                        "__v":0
                     },
                     "_id":"63a5bcc57433d072405cde8b"
                  }
               ],
               "createdAt":"2022-12-23T14:35:49.735Z",
               "updatedAt":"2022-12-23T14:58:06.156Z",
               "__v":0
            }
         ],
         "images":{
            "_id":"63a5bcc57433d072405cde87",
            "product":"63a5bcc57433d072405cde86",
            "color":"6398fbdb1fc0f2835e898a91",
            "images":[
               {
                  "_id":"6375c3d97d5dc191a77ca7ac",
                  "name":"boutiqueb-hahhaha-1668662233138.jpeg",
                  "displayName":"hahhaha",
                  "createdAt":"2022-11-17T05:17:13.159Z",
                  "updatedAt":"2022-11-17T05:17:13.159Z",
                  "__v":0
               },
               {
                  "_id":"6375c40d7d5dc191a77ca7c2",
                  "name":"boutiqueb-paypal-1668662285552.jpeg",
                  "displayName":"paypal",
                  "createdAt":"2022-11-17T05:18:05.570Z",
                  "updatedAt":"2022-11-17T05:18:05.570Z",
                  "__v":0
               }
            ],
            "createdAt":"2022-12-23T14:35:49.561Z",
            "updatedAt":"2022-12-23T14:35:49.561Z",
            "__v":0
         },
         "createdAt":"2022-12-23T14:35:49.897Z",
         "updatedAt":"2022-12-23T14:35:49.897Z",
         "__v":0
      },
      {
         "_id":"63a5bcc67433d072405cde95",
         "color":{
            "_id":"639b75a799cf2ed9eb2802ec",
            "name":"dr",
            "code":"#301313",
            "skuCode":"002",
            "createdAt":"2022-12-15T19:29:43.798Z",
            "updatedAt":"2022-12-15T19:29:43.798Z",
            "__v":0
         },
         "size":{
            "_id":"639b759899cf2ed9eb2802e4",
            "name":"medium",
            "skuCode":"Z",
            "createdAt":"2022-12-15T19:29:28.047Z",
            "updatedAt":"2022-12-15T19:29:28.047Z",
            "__v":0
         },
         "stock":[
            {
               "_id":"63a5bcc67433d072405cde91",
               "available":2,
               "reserved":0,
               "store":0,
               "labourCost":{
                  "_id":"63a1deb1d5890e2103410bf4",
                  "title":"Ali Boxers Maker",
                  "description":"Ali needs his boxers hand made",
                  "amount":20,
                  "createdAt":"2022-12-20T16:11:29.690Z",
                  "updatedAt":"2022-12-22T17:06:37.667Z",
                  "__v":0
               },
               "materials":[
                  {
                     "amount":1,
                     "material":null,
                     "_id":"63a5bcc67433d072405cde92"
                  },
                  {
                     "amount":1,
                     "material":{
                        "_id":"639a44fc5a053c1a05e60b44",
                        "name":"test emile",
                        "description":"test test",
                        "stock":2,
                        "supplier":"emile",
                        "costPerUnit":1,
                        "fabric":"cotton",
                        "createdAt":"2022-12-14T21:49:48.322Z",
                        "updatedAt":"2022-12-25T15:46:03.105Z",
                        "__v":0,
                        "image":"63a8703adb0f557f329cabfa"
                     },
                     "_id":"63a5bcc67433d072405cde93"
                  }
               ],
               "createdAt":"2022-12-23T14:35:50.229Z",
               "updatedAt":"2022-12-23T14:35:50.229Z",
               "__v":0
            }
         ],
         "images":{
            "_id":"63a5bcc67433d072405cde8f",
            "product":"63a5bcc57433d072405cde86",
            "color":"639b75a799cf2ed9eb2802ec",
            "images":[
               {
                  "_id":"6375c3d97d5dc191a77ca7ac",
                  "name":"boutiqueb-hahhaha-1668662233138.jpeg",
                  "displayName":"hahhaha",
                  "createdAt":"2022-11-17T05:17:13.159Z",
                  "updatedAt":"2022-11-17T05:17:13.159Z",
                  "__v":0
               },
               {
                  "_id":"6375c40d7d5dc191a77ca7c2",
                  "name":"boutiqueb-paypal-1668662285552.jpeg",
                  "displayName":"paypal",
                  "createdAt":"2022-11-17T05:18:05.570Z",
                  "updatedAt":"2022-11-17T05:18:05.570Z",
                  "__v":0
               }
            ],
            "createdAt":"2022-12-23T14:35:50.067Z",
            "updatedAt":"2022-12-23T14:35:50.067Z",
            "__v":0
         },
         "createdAt":"2022-12-23T14:35:50.395Z",
         "updatedAt":"2022-12-23T14:35:50.395Z",
         "__v":0
      }
   ],
   "price":50,
   "discount":0,
   "identifier":"A",
   "createdAt":"2022-12-23T14:35:50.556Z",
   "updatedAt":"2022-12-23T14:35:50.556Z",
   "__v":0
}
#9

your values in your documents are no longer a collection of ids after population. they now are objects with their own _id fields and you now need to query them accordingly.

  • “department._id”
  • “category._id”
  • “variations.color._id”
  • “variations.size._id”

by the way, it is weird your id values seem to be stored as mere strings, not as $oid as you showed previously. are you using versioning in models and queries? try to get also another sample with __v:1 for example and see how they differ.

one more thing about formatting your posts. they are long and makes it hard to follow when someone new comes in. from the :gear: icon in the editor, select “Hide Details” and put your “code” sections in it and edit the text it show to reflect the content. chek the below one for example.

Click here to see hidden detail I mentioned

this way it will be easier to read when your sample code starts to be lengthy

#10

it appears as $oid when I copy it from my document itself. But what I sent was the result of my findOne() and in this case it appears like that.

department an category were working. I didn’t need to add _id for it to work. In fact, when I tried it with them, it stopped working.

As for color and size, it didn’t work this way either.

EDIT: I removed all versionKeys from my models, still didn’t work.

#11

I might have a mistake as I failed to find my reference line about this: if you use “exec”, previous stages were first combined and then executed. I had the idea that documents are first populated and find operation was applied. I could even misunderstand that line. I will check this later on.

anyways, for now, let’s try to check your use of the code. unlike for color and size, you don’t seem to convert any id field for department and category. so, how do you use your buildProductQuery function and what are the values of these variables: { department, category, color, size } = query (edit sensitive information)

#12

department: 6398fbf11fc0f2835e898aa8
category: 6398fbfd1fc0f2835e898aae

When I try to filter with any (or both) of the above, it’s working perfectly.

color: 639b75a799cf2ed9eb2802ec
size: 639b759899cf2ed9eb2802e4

When I try to filter with any (or both) of the above, it’s not working.
Here is a query example for when I filter with color:

Example 
{
  variations: { '$ne': [] },
  'variations.color': '639b75a799cf2ed9eb2802ec'
}
#13

to play with, I modified/minified your model, query, populate query, and data. I kept only id fields and a few keys such as names.

It seems mongoose takes string representation of ObjectId as input and converts it internally depending on the model properties. for example the following query on variations collection gets correct color object then populates it.

await Variation.find({"color":"639b75a799cf2ed9eb2802ec"}).populate("color")

its output is

[
  {
    _id: new ObjectId("63a5bcc67433d072405cde95"),
    color: { _id: new ObjectId("639b75a799cf2ed9eb2802ec"), name: 'dr' },
    size: new ObjectId("639b759899cf2ed9eb2802e4")
  }
]

this explains why the department and category of your query work without manually converting them to ObjectId.

as for the sub-fields, I found out that a matching query is possible by the populate method itself. the following link
Mongoose v6.8.1: Query (mongoosejs.com)

here, I embed the match query for the color into the populate query:

let found = await Product.find()
        .populate({
            path: "variations",
            model: this.Variation,
            match:{ "color": "639b75a799cf2ed9eb2802ec" },
            populate: [
                {
                    path: "color",
                    model: this.Color,
                },
                {
                    path: "size",
                    model: this.Size,
                }
            ]
        })

giving the following result:

{
    "_id": "63a5bcc57433d072405cde86",
    "name": "Ali's Boxers",
    "description": "Boxers made specifically for ALi",
    "department": "6398fbf11fc0f2835e898aa8",
    "category": "6398fbfd1fc0f2835e898aae",
    "variations": [
        {
            "_id": "63a5bcc67433d072405cde95",
            "color": {
                "_id": "639b75a799cf2ed9eb2802ec",
                "name": "dr"
            },
            "size": {
                "_id": "639b759899cf2ed9eb2802e4",
                "name": "medium"
            }
        }
    ]
}

This means removing color and size from your buildProductQuery method and creating a new populate query builder to use them.

I don’t know if any other method exists but this seems pretty easy to implement.

by the way, I don’t know if it is mongoose or JSON.stringfy but I now can see why your sample data had strings in place of ObjectIds:

// actual object{
  _id: new ObjectId("63a5bcc57433d072405cde86"),
  name: "Ali's Boxers",
  description: 'Boxers made specifically for ALi',
  department: new ObjectId("6398fbf11fc0f2835e898aa8"),
  category: new ObjectId("6398fbfd1fc0f2835e898aae"),
  variations: [
    new ObjectId("63a5bcc57433d072405cde8d"),
    new ObjectId("63a5bcc67433d072405cde95")
  ]
}
// JSON.stringify applied
{
  "_id": "63a5bcc57433d072405cde86",
  "name": "Ali's Boxers",
  "description": "Boxers made specifically for ALi",
  "department": "6398fbf11fc0f2835e898aa8",
  "category": "6398fbfd1fc0f2835e898aae",
  "variations": ["63a5bcc57433d072405cde8d", "63a5bcc67433d072405cde95"]
}
#14

That seems like a good idea. However if a color is not found in a variations array, it will still return an empty array, which defies the whole point of variations: { $ne: [] } in my filters, and I do not with for this to happen.

Moreover, I am using countDocuments(queryObj) to count how many items with a certain filter I have so I can create a pagination. Using your suggestedway kinda ruins it? Unless there is something I am missing.

Here is my function, with the way you suggested, using buildProductPopulateQuery(req.query), in case you need to take a look:

Get All Products 
exports.getAll = async (req, res) => {
  try {
    const { offset, limit } = req.query;
    const offsetInt = parseInt(offset) || 0;
    const limitInt = parseInt(limit) || 15;

    const queryObj = buildProductQuery(req.query);
    const populateQuery = buildProductPopulateQuery(req.query);

    console.log(JSON.stringify(populateQuery));

    const products = await Product.find(queryObj)
      .limit(limitInt)
      .skip(offsetInt)
      .populate(populateQuery);

    const totalProductsCount = await Product.countDocuments(queryObj);
    const totalPages = Math.ceil(totalProductsCount / limitInt);
    const currentPage = Math.ceil(offsetInt / limitInt) + 1;

    return Response.success(res, {
      products,
      pagination: {
        total: totalProductsCount,
        pages: totalPages,
        current: currentPage,
      },
    });
  } catch (err) {
    return Response.serverError(res, err.message);
  }
};
#15

Alright I figured out a way using aggregate()

getAll 
exports.getAll = async (req, res) => {

  try {

    const { offset, limit } = req.query;

    const offsetInt = parseInt(offset) || 0;

    const limitInt = parseInt(limit) || 15;

    const query = {

      departments: req.query.departments

        ? JSON.parse(req.query.departments)

        : null,

      categories: req.query.categories

        ? JSON.parse(req.query.categories)

        : null,

      colors: req.query.colors ? JSON.parse(req.query.colors) : null,

      sizes: req.query.sizes ? JSON.parse(req.query.sizes) : null,

    };

    const pipeline = buildProductPipeline(query);

    const countPipeline = buildCountProductPipeline(query);

    const products = await Product.aggregate(pipeline)

      .skip(offsetInt)

      .limit(limitInt);

    const totalProductsCount =

      (await Product.aggregate(countPipeline))[0]?.count || 0;

    const totalPages = Math.ceil(totalProductsCount / limitInt);

    const currentPage = Math.ceil(offsetInt / limitInt) + 1;

    return Response.success(res, {

      products,

      pagination: {

        total: totalProductsCount,

        pages: totalPages,

        current: currentPage,

      },

    });

  } catch (err) {

    return Response.serverError(res, err.message);

  }

};
buildProductPipeline 
const buildProductPipeline = (query) => {
  const { departments, categories, colors, sizes } = query;

  return [
    {
      $match: {
        ...(departments && {
          department: {
            $in: departments.map((d) => mongoose.Types.ObjectId(d)),
          },
        }),
        ...(categories && {
          category: { $in: categories.map((c) => mongoose.Types.ObjectId(c)) },
        }),
      },
    },
    {
      $lookup: {
        from: "variations",
        localField: "variations",
        foreignField: "_id",
        as: "variations",
        pipeline: [
          {
            $match: {
              ...(colors && {
                color: {
                  $in: colors.map((c) => mongoose.Types.ObjectId(c)),
                },
              }),
              ...(sizes && {
                size: {
                  $in: sizes.map((s) => mongoose.Types.ObjectId(s)),
                },
              }),
            },
          },
          {
            $lookup: {
              from: "colors",
              localField: "color",
              foreignField: "_id",
              as: "color",
            },
          },
          {
            $unwind: {
              path: "$color",
              preserveNullAndEmptyArrays: true,
            },
          },
          {
            $lookup: {
              from: "sizes",
              localField: "size",
              foreignField: "_id",
              as: "size",
            },
          },
          {
            $unwind: {
              path: "$size",
              preserveNullAndEmptyArrays: true,
            },
          },
          {
            $lookup: {
              from: "stocks",
              localField: "stock",
              foreignField: "_id",
              as: "stock",
              pipeline: [
                {
                  $lookup: {
                    from: "labourcosts",
                    localField: "labourCost",
                    foreignField: "_id",
                    as: "labourCost",
                  },
                },
                {
                  $unwind: {
                    path: "$labourCost",
                    preserveNullAndEmptyArrays: true,
                  },
                },
                {
                  $lookup: {
                    from: "materials",
                    as: "materials",
                    pipeline: [
                      {
                        $lookup: {
                          from: "materials",
                          localField: "material",
                          foreignField: "_id",
                          as: "material",
                        },
                      },
                      {
                        $unwind: {
                          path: "$material",
                          preserveNullAndEmptyArrays: true,
                        },
                      },
                    ],
                  },
                },
              ],
            },
          },
        ],
      },
    },
    {
      $match: {
        variations: { $ne: [] },
      },
    },
    {
      $lookup: {
        from: "departments",
        localField: "department",
        foreignField: "_id",
        as: "department",
      },
    },
    {
      $unwind: {
        path: "$department",
        preserveNullAndEmptyArrays: true,
      },
    },
    {
      $lookup: {
        from: "categories",
        localField: "category",
        foreignField: "_id",
        as: "category",
      },
    },
    {
      $unwind: {
        path: "$category",
        preserveNullAndEmptyArrays: true,
      },
    },
  ];
};
buildCountProductPipeline 
const buildCountProductPipeline = (query) => {
  const { departments, categories, colors, sizes } = query;

  return [
    {
      $match: {
        ...(departments && {
          department: {
            $in: departments.map((d) => mongoose.Types.ObjectId(d)),
          },
        }),
        ...(categories && {
          category: { $in: categories.map((c) => mongoose.Types.ObjectId(c)) },
        }),
      },
    },
    {
      $lookup: {
        from: "variations",
        localField: "variations",
        foreignField: "_id",
        as: "variations",
      },
    },
    {
      $match: {
        ...(colors && {
          "variations.color": {
            $in: colors.map((c) => mongoose.Types.ObjectId(c)),
          },
        }),
        ...(sizes && {
          "variations.size": {
            $in: sizes.map((s) => mongoose.Types.ObjectId(s)),
          },
        }),
      },
    },
    {
      $group: {
        _id: "$_id",
        count: { $sum: 1 },
      },
    },
    {
      $count: "count",
    },
  ];
};
1 Like
#16

lingering references to non-existence variations are not good. this need for checking empty variations shows signs of bad design, either on the creation of a product or accessing the database outside the client app. if you have authority over the design you may consider refactoring your design.

anyways, it seems you may have two occurrences where you will have an empty variations array. the array can be empty at creation (or later updated), and “populate” may result in an empty array. for the first one include variations: { $ne: [] } in “buildProductQuery” and then use vanilla js filtering on the result for the second case:

    products = (await Product.find({"variations":{"$ne":[]}}).populate("variations"))
               .filter(function (product) { return product["variations"].length>0; })

however, keep in mind that this works on the app’s host’s memory so use .lean() to reduce memory usage. And again, I still don’t know if any other method exists. Until we find a better way (if any), this one handles the jobs pretty well.

One way might be to leverage the $lookup of mongodb through Model.aggregate where you have more (or total) control over your query to run on the database, but you need to spend some time learning it as it gets complicated at times. you can also leverage the counting documents and creating pagination on the database server itself. the bad news is, I use aggregation, but I haven’t used it with mongoose, so that beats me here.

#17

We do not want to delete a product if it has no variations, as the admins can always add new ones. what causes the variations to be empty is when they get archived and moved to the archive table. In that canse, we want the product to still be available so we reference to it by id.

Archive Model 
{
  product: {
    type: mongoose.Schema.Types.ObjectId,
    ref: "Product",
    required: true,
  },
  variation: {
    type: mongoose.Schema.Types.ObjectId,
    ref: "Variation",
    required: true,
  },
}
1 Like
#18

I did not notice your second post came while I was writing :slight_smile:

you have a nice pipeline there. I don’t know how mongoose glues “models” after using aggregation, but if it does the job let it do the job, and if so, then you should mark it as “the accepted answer” so the community can see this problem is solved :wink:

1 Like
#19

Went ahead and did just that.

Thanks again for your help, really appreciate your time.

1 Like
#20

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.