Prisma ORM | MongoDB - how to group documents by mutiple attributes and list the rest of them-CodePudding

I'm developing a quite simple backend application with Express, using Prisma ORM to connect to a MongoDB database.

The events collection is filled with documents such as:

{
  "_id": string,
  "class_code": string
  "class_type": string,
  "created_by": string,
  "end_period": date,
  "end_time": time,
  "has_to_be_allocated": boolean
  "pendings": int,
  "preferences": {
    "accessibility": boolean
    "air_conditioning": boolean,
    "building": string,
    "projector": boolean
  },
  "start_period": date,
  "start_time": time,
  "subject_code": string,
  "subject_name": stirng,
  "subscribers": int,
  "updated_at": timestamp,
  "vacancies": int,
  "week_day": string,
  "building": string,
  "classroom": string
}

My prisma schema is:

type Preferences {
  accessibility    Boolean?
  air_conditioning Boolean?
  building         String?
  projector        Boolean?
}

model events {
  id                  String       @id @default(auto()) @map("_id") @db.ObjectId
  class_code          String
  subject_code        String
  subject_name        String
  week_day            String
  class_type          String
  start_period        String
  end_period          String
  start_time          String
  end_time            String
  has_to_be_allocated Boolean
  pendings            Int
  subscribers         Int
  vacancies           Int
  created_by          String
  updated_at          String
  preferences         Preferences?
  professor           String?
  classroom           String?
  building            String?
}

Multiple different documents could have the same class_code and subject_code, but different week_day, start_time, end_time, building and classroom attributes.

My intention is to perform a single query so that I end up with a list of objects like such:

{
   "subject_name":"Subject name",
   "subject_code":"Subject code",
   "class_code":"1",
   "professor":"professor name",
   "start_period":"2023-03-13",
   "end_period":"2023-07-15",
   "schedule":[
      {
         "id":"1",
         "week_day":"monday",
         "start_time":"09:20",
         "end_time":"11:00",
         "building":"building 1",
         "classroom":"C2-03"
      },
      {
         "id":"2",
         "week_day":"thursday",
         "start_time":"07:30",
         "end_time":"09:10",
         "building":"building 2",
         "classroom":"C2-08"
      },
      {
         "id":"3",
         "week_day":"friday",
         "start_time":"07:30",
         "end_time":"09:10",
         "building":"building 3",
         "classroom":"C2-04"
      }
   ]
}

That is, I want to group the documents by subject_code and class_code simultaneously, while listing the rest of the differing information between the documents as schedule.

I could always fetch all documents and algorithmically create the object I need, but that would be rather inefficient (and lame).

Any thoughts on how to perform such task? I've been playing around with Prisma's groupBy API but without any success - needless to say, I'm new to it.

So far all I've got is a route which filters the database by subject_code and class_code, and that works fine:

const classInfo = await prisma.events.findMany({
  where: {
    subject_code: 
      equals: subjectCode as string,
    },
    class_code: {
      endsWith: fullClassCode,
    },
  },
});

I then map the retrieved object to the format I need:

const classDetail = {
  subjectName: classInfo?.[0]?.subject_name,
  subjectCode: classInfo?.[0]?.subject_code,
  classCode: getAbbreviatedClassCode(classInfo?.[0]?.class_code),
  professor: classInfo?.[0]?.professor,
  startPeriod: classInfo?.[0]?.start_period,
  endPeriod: classInfo?.[0]?.end_period,
  schedule: classInfo.map((event: Event) => ({
    id: event?.id,
    weekDay: mapWeekDays(event?.week_day),
    startTime: event?.start_time,
    endTime: event?.end_time,
    building: event?.building,
    classroom: event?.classroom,
  })),
};

What I need is to list all combinations of subject_code and class_code while obtaining objects like the one I mentioned above.

CodePudding user response：

Ok I am no expert in prisma or mongodb but you can try the following (If this basic query works, just modify it to include the additional fields):

const agg = [
  {
    '$group': {
      '_id': {
        'class_code': '$class_code', 
        'subject_code': '$subject_code'
      }, 
      'schedule': {
        '$push': {
          'week_day': '$week_day', 
          'start_time': '$start_time'
        }
      }
    }
  }
]

const result = await prisma.events.aggregateRaw({
  pipeline: agg,
});

References:

CodePudding user response：

What does your events prisma schema look like? (IE, how is it defined in the schema.prisma file? Also, is the schedule a separate collection or are these entries simply embedded documents?