0

I have two collections with the following schema:

product_categories

interface Category {
  _id: string;
  name: string;
  parentId: string;
}

This collection is hierarchical.

Example Data
  1. Components
    1. CPU
    2. GPU
    3. RAM
    4. MOBO
  2. Accessories
    1. Keyboard
    2. Mouse
    3. Headphone

products

interface Product {
  _id: string;
  name: string;
  categoryId: string;

  // ... other irrelevant fields ...
}

I want to filter products by categoryId. For example: if I'm searching with the _id of Components category then the query should also find products from sub-categories like "CPU", "GPU" etc.

I've managed to come up with a query to find sub-categories recursively with the $graphLookup stage.

db.product_categories.aggregate([
  { $match: { _id: "<parent-category-id-here>" } },
  {
    $graphLookup: {
      as: "children",
      startWith: "$_id",
      connectFromField: "_id",
      connectToField: "parentId",
      from: "product_categories",
    },
  },
  { $unwind: "$children" },
  { $project: { _id: 1, childId: "$children._id" } },
]);

But, I don't know how to use it in the $match stage of the following query.

db.products.aggregate([
  {
    $match: {
      categoryId: {
        $in: [
          "<parent-category-id>",
          "<sub-category-ids-from-the-$graphLookup-stage>",
        ],
      },
    },
  },
  // ... other stages
]);

I know that, I can just fetch the subcategories before running second query but I'm trying to do it all in one go. Is it possible?

Please give me some hint about how can I proceed further from this step. Is there a better alternative solution to this problem?

Thanks in advance, I highly appreciate your time on SO .

h-sifat
  • 1,455
  • 3
  • 19

0 Answers0