Prisma ORM | MongoDB -如何按多个属性分组文档并列出其余部分

Question

我正在用Express开发一个非常简单的后端应用程序，使用Prisma ORM连接到MongoDB数据库。

events集合由以下文档填充:

{
"_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
}

我的prisma schema是:

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?
}

多个不同的文档可能具有相同的class_code和subject_code，但week_day,start_time,end_time,building和classroom属性不同。

我的目的是执行一个查询，这样我最终得到一个对象列表，如:

{
"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"
}
]
}

也就是说，我想同时按subject_code和class_code对文档进行分组，同时将文档之间的其他不同信息列为schedule。

我总是可以获取所有文档并通过算法创建我需要的对象，但这将是相当低效的(和蹩脚的)。

对如何完成这样的任务有什么想法吗?我一直在玩Prisma的groupBy API，但没有任何成功-不用说，我是新手。

到目前为止，我所得到的是一个路由过滤数据库的subject_code和class_code，这工作得很好:

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

然后将检索到的对象映射为我需要的格式:

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,
})),
};

我需要的是列出subject_code和class_code的所有组合，同时获得像我上面提到的对象。

Answer 1

好吧，我不是prism或mongodb的专家，但你可以尝试以下(如果这个基本查询工作，只需修改它以包括额外的字段):

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,
});

引用:

• https://www.mongodb.com/docs/manual/reference/operator/update/push/
• https://www.mongodb.com/docs/manual/reference/operator/aggregation/group/pivot-data

Answer 2

您的events棱镜模式看起来像什么?(IE，它是如何定义在schema.prisma文件?另外，schedule是一个单独的集合，还是这些条目只是嵌入文档?