I currently have an academic year start date and end date. I want to get student data for every week during this academic year. Currently i have created an array which contains all the start and end dates of every week and i am looping through each week and calling the db each time like this:

let allWeeksData = []

  let groupQuery: any = {
    _id: {
      attendance_code: "$attendance_code",
    },
    total: { $sum: 1 },
  };

for(let dateRanges of dateRangesArray)
{
  const startDate = dateRanges.start_date;
  const endDate = dateRanges.end_date;

  const rawResults = await sessionAttendanceModel.aggregate([
    {
      $match: {
        student_school: { $in: studentSchoolIDs },
        "date.date": {
          $gte: new Date(startDate),
          $lte: new Date(endDate),
        },
        attendance_code: {
          $in: usedAttendanceCodes,
        },
      },
    },
    {
      $group: groupQuery,
    },
  ]);
  rawResults.start_date = startDate
  rawResults.end_date = endDate
  allWeeksData.push(rawResults)
}

However this is quite slow. Is there a way to call the db only once using an aggregate group and get the same end result?

CodePudding user response：

There is an operator $week to get the week of the year from a date, you can have a $group stage to group by the weeks of the year:

{
  $group: {
    _id: {
      "week": {
        $week: "$date"
      },
      "year": {
        $year: "$date"
      }
    }
  }
}

Mongo playground

CodePudding user response：

Maybe group by this:

{
   _id: {
      attendance_code: "$attendance_code",
      date: {
         $dateTrunc: {
            date: "$date.date",
            unit: "week",
            startOfWeek: "monday"
         }
      },
   },
   total: { $sum: 1 },
}

CodePudding user response：

You can $group everything by weeks and then in $project pipeline you can formulate exit values by your needs

Your aggregation would have something like this

[{
    $group:{
        _id:{
            $week: "$date.date"
        },
        data:{
            $first: "$$ROOT"
        }
    }
}]