如何从prisma中的自关系表查询中获取深度嵌套json

Question

我想用prisma ORM动态创建一个深度嵌套菜单，并将所有带有子菜单的菜单作为深度嵌套的json对象这是我的代码，我已经尝试过查询，但结果不是我想要的

这是我的prismaSchema文件

model Menu {
id            Int    @id @default(autoincrement())
title         String
url           String @default("#")
parentMenu    Menu?  @relation("parentSubCategory", fields: [parrentMenuId], references: [id])
subMenu       Menu[] @relation("parentSubCategory")
parrentMenuId Int?
}

我想要一个查询来获得一个具有深度嵌套菜单和子菜单的json，如下面的

{
title:"menu1",
subMenu:[
{
title:"sub1"
subMenus:[
{
title:"sub3",
subMenus:[
{
title:"sub4"
}
]
} 
]  
}
]
}

我已经尝试了下面的查询，但结果不是我想要的

const result = await prisma.menu.findMany({
select: {
id: true,
title: true,
url: true,
subMenu: true,
},
});

我的查询结果如下

"data": [
{
"id": 1,
"title": "sample",
"url": "#",
"subMenu": [
{
"id": 2,
"title": "digital",
"url": "#",
"parrentMenuId": 1
}
]
},
{
"id": 2,
"title": "digital",
"url": "#",
"subMenu": [
{
"id": 3,
"title": "pc & laptop",
"url": "#",
"parrentMenuId": 2
}
]
},
{
"id": 3,
"title": "pc & laptop",
"url": "#",
"subMenu": [
{
"id": 4,
"title": "pc",
"url": "#",
"parrentMenuId": 3
},
{
"id": 5,
"title": "laptop",
"url": "#",
"parrentMenuId": 3
}
]
},
{
"id": 4,
"title": "pc",
"url": "#",
"subMenu": [
{
"id": 6,
"title": "pc parts",
"url": "#",
"parrentMenuId": 4
}
]
},
{
"id": 5,
"title": "laptop",
"url": "#",
"subMenu": []
},
{
"id": 6,
"title": "pc parts",
"url": "#",
"subMenu": [
{
"id": 7,
"title": "ram and storage",
"url": "#",
"parrentMenuId": 6
}
]
},

有人能帮我吗？

Answer 1

只需在此嵌套结构中添加该表中存在的尽可能多的子菜单。

const result = await prisma.menu.findMany({
select: {
id: true,
title: true,
url: true,
subMenu: { //first-sub menu
id: true,
title: true,
url: true,
subMenu: { //2nd one
id: true,
title: true,
url: true,
subMenu: { // 3rd one
id: true,
title: true,
url: true,
subMenu: true // Presumably the last sub menu
}
}
},
},
});