Sunday, 7 August 2022

Given an id of a document with recursive field `children`, find all documents that reference the document or any of its children

I have a collection of product folders productfolders and a collection of products products.

const ProductFolderSchema = new Schema(
  {
    folderName: { type: String, required: true },
    parent: { type: Schema.Types.ObjectId, ref: 'ProductFolder' },
    children: [{ type: Schema.Types.ObjectId, ref: 'ProductFolder' }],
  }
);

const ProductSchema = new Schema<TProductSchema>(
  {
    productName: String,
    folder: { type: Schema.Types.ObjectId, ref: 'ProductFolder' },
  },
);

I have a backend that receives query parameter folderId and should return all products whose folder property is equal to folderId or is a descendant of folder with _id of folderId (meaning folder is one of the children of folder with _id of folderId - children can be nested deep inside children's children).

For example, consider collections productfolders and products that look like this:

const productfolders = [
  {
    "_id": "62e74dac78c13b738874e1a9",
    "folderName": "Weapons",
    "children": [
      {
        "_id": "62e74dd278c13b738874e1ac",
        "folderName": "Bows",
        "parent": "62e74dac78c13b738874e1a9",
        "children": [
          {
            "_id": "62e74ddb78c13b738874e1b1",
            "folderName": "Long Bows",
            "parent": "62e74dd278c13b738874e1ac",
            "children": [],
          },
          {
            "_id": "62e74de278c13b738874e1b7",
            "folderName": "Short Bows",
            "parent": "62e74dd278c13b738874e1ac",
            "children": [],
          }
        ],
      },
    ]
  }
];

const products = [
  {
    "productName": "Curved Bow",
    "folder": "62e74de278c13b738874e1b7",
    "_id": "62e237368fbde6ed77e3e489"
  }
];

When I pass folderId of 62e74dac78c13b738874e1a9 ("folderName": "Weapons"), I want "Curved Bow" product to be found because its folder is a deep children of "Weapons" folder.

I think you can only search something in recursive structures using $graphLookup but I couldn't figure out how to pass the variable folderId to its startsWith operator(sorry if I'm using the wrong naming of things)

Here's example db: https://mongoplayground.net/p/Yxps44cfG28

Here's my code that doesn't find anything:

const products = await ProductModel.aggregate([
  {
    $graphLookup: {
      from: 'productfolders',
      startWith: folderId, // can only pass mongo expressions here, not working with variables
      connectFromField: '_id',
      connectToField: 'children',
      as: 'output',
    },
  },
]);

How do I find all products whose folder property is equal to or is a deep children of folder with folderId?



from Given an id of a document with recursive field `children`, find all documents that reference the document or any of its children

No comments:

Post a Comment