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
?
2
Answers
Based on this question by @rickhg12hs, you can use
$function
for this recursive search to get an array of nested folders. Then all is left is to use a regular$lookup
:See how it works on the playground example
Your search was quite close. I guess the confusion came from having both parent and children fields in your schema.
As I mentioned in the comment, I don’t see how you keep children up to date for all parents when you add a new folder to one of the children, but I will leave it with you. For now I will just ignore the children array.
parent
is enough for $graphLookup:Here $graphLookup builds a flat array of all parents for each product:
startWith
is thefolder
fromproducts
documentconnectToField
is the corresponding field inproductfolders
collectionproductfolders
is thee field of theproductfolders
document to use in the next recursive call instead of thestartWith
So the
path
array for theFireworks ("folder": "62e7bead91041bdddf25dd4b")
will be:Do you see the chain –
parent
of the document matches_id
of the next in the chain, right?So after the $graphLookup stage you have full folder path from the root to the product’s folder for each product. Now you just $match products that do have the folder in question anywhere in the chain.
There is a simplified example on https://mongoplayground.net/p/Cy-_SzzcdNT