'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,
- Get all
ModelA's that haveUserIdthat I specify, and - Get all
ModelCs count grouped by a status{ $group: { _id: "$status", count: { $sum: 1 } } }, that belong toModelBthat belongs toModelA
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 |
|---|
