'MongoDB, get documents by a field, and mergeObject from another collections grouped count

Okay, the title is a bit too verbose and complicated but I have no idea how to put it any better. So here's what I have

ModelA

ModelB that has reference to ModelA with ModelAId

ModelC that has reference to ModelB with ModelBId

So User has many ModelA has many ModelB has many ModelC

I need to,

  1. Get all ModelA's that have UserId that I specify, and
  2. Get all ModelCs count grouped by a status { $group: { _id: "$status", count: { $sum: 1 } } }, that belong to ModelB that belongs to ModelA

I've tried to aggregate this but got lost along the way, the type I'd like to return is

type ModelAWithModelCCount {
   a: ModelA,
   count: {
     status1: number;
    status2: number;...
  }
}

I currently have N+1 problem where for every ModelA I'd have to go and fetch ModelB's and then ModelC's in a loop, which would result in too many trips to the DB.

What I've tried (also missing the part of userId)

   const modelAs = ModelA.aggregate<{
            a: ModelA;
            count: {
                approved: number;
                rejected: number;
                pending: number;
            };
        }>([
            {
                $lookup: {
                    from: "modelB",
                    localField: "_id",
                    foreignField: "modelAId",
                    as: "b",
                },
            },
            {
                $lookup: {
                    from: "modelC",
                    localField: "b._id",
                    foreignField: "modelBId",
                    as: "c",
                },
            },
            {
                $replaceRoot: {
                    newRoot: {
                        $mergeObjects: [
                              , // Get the count from c grouped by status 
                            "$$ROOT",
                        ],
                    },
                },
            },
        ]);


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source