Hi there!
it is my first time in mongo db. I would like to perform below tradition sql query in mongo.
WITH temp AS (
SELECT
l.mholidayId,
SUM(l.totalLeaveDays) AS usedDay,
YEAR(l.created_at) AS year
FROM leaves l
WHERE l.empId = @empId AND YEAR(l.created_at) = 2025
GROUP BY l.mholidayId, YEAR(l.created_at)
)
SELECT
mh.id,
mh.holidayName,
mh.totalDay,
COALESCE(t.usedDay, 0) AS usedDay,
COALESCE(t.year, 2025) AS year
FROM mholidays mh
LEFT JOIN temp t ON mh.id = t.mholidayId
WHERE mh.flag = ‘Active’ AND mh.isDeleted = FALSE;
I expect to get all list from left table (mholidays) if nothing match on right table leaves. I have mholidays & leaves collections join on mholidays._id = leaves.mholidayId. I tried many ways on google but get nothing or always empty array in result while both collects have data. I am using Nodejs + grapql with below shared source code which clarify with ChatGPT.
holidayByUser: async (_, { where }, { models, req }) => {
try {
const verifyToken = tokenVerification(req);
if (!verifyToken.isValid) {
throw config.statusMessage.PERMISSION_DENIED;
}
const _where = {
empId: where?.empId || verifyToken?.data?.id || undefined
};
if (!_where.empId) {
throw new Error("empId is required");
}
const result = await models.mholidayModel.aggregate([
// Step 1: Filter active and non-deleted holidays
{
$match: {
flag: "active",
isDeleted: { $ne: true } // Handle cases where isDeleted might not exist
}
},
// Step 2: Left join with the leave collection
{
$lookup: {
from: "leave", // Ensure this matches your actual collection name
localField: "_id",
foreignField: "mholidayId",
as: "leaveData"
}
},
// Step 3: Unwind leaveData to process individual leave records
{
$unwind: {
path: "$leaveData",
preserveNullAndEmptyArrays: true // Ensures all holidays are kept
}
},
// Step 4: Filter leave records by empId, keeping unmatched holidays
{
$match: {
$or: [
{ "leaveData.empId": new mongoose.Types.ObjectId(_where.empId) },
{ "leaveData": null } // Include holidays with no leave records
]
}
},
// Step 5: Group by holiday and calculate totals
{
$group: {
_id: "$_id",
holidayName: { $first: "$holidayName" },
totalDay: { $first: "$totalDay" },
usedDay: {
$sum: {
$cond: [
{ $eq: ["$leaveData", null] },
0,
"$leaveData.totalLeaveDay"
]
}
}
}
},
// Step 6: Project the final fields
{
$project: {
id: "$_id",
holidayName: 1,
totalDay: 1,
usedDay: 1,
remainDay: { $subtract: ["$totalDay", "$usedDay"] }
}
}
]);
console.log("Aggregated Result:", JSON.stringify(result, null, 2));
return result;
} catch (error) {
console.error("Error in holidayByUser:", error);
throw new Error(error.message || "Failed to fetch holiday data");
}
}