MIYACHIN
Posted on March 17, 2022
I had a problem when I wanted to do a further JOIN on JOINed records, so I looked up how to do it.
Consider these table structure.
(For simplicity, createdAt and updatedAt are omitted.)
group
- group_id
- group_name
member
- member_id
- member_name
- group_id
post
- post_id,
- post_content,
- member_id
Each group has multiple members, and each member has multiple posts.
On schema.prisma, it is defined like this.
model Group {
groupId Int @id @default(autoincrement()) @map("grooup_id")
groupName String @map("group_name") @db.VarChar(200)
members Member[]
@@map("group")
}
model Member {
memberId Int @id @default(autoincrement()) @map("member_id")
memberName String @map("member_name") @db.VarChar(200)
group Group @relation(fields: [groupId], references: [groupId])
groupId Int @map("group_id")
posts Post[]
@@map("member")
}
model Post {
postId Int @id @default(autoincrement()) @map("post_id")
postContent String @map("post_content") @db.VarChar(255)
member Member @relation(fields: [memberId], references: [memberId])
memberId Int @map("member_id")
@@map("post")
}
By the way, if you run prisma format, it aligns the vertical lines of shcema.prisma nicely!
How to query
With Nest.js, you can code it like this.
import { Injectable } from '@nestjs/common';
import { PrismaService } from './../prisma.service';
import { Group, Prisma } from '@prisma/client';
@Injectable()
export class GroupService {
constructor(private prisma: PrismaService) {}
findOne(id: number) {
return this.prisma.group.findUnique({
where: { groupId: id },
include: {
members: {
include: {
post: true
},
},
},
});
}
}
And the response will look like this.
{
"groupId":1,
"groupName":"sample group",
"members":[
{
"memberId":1,
"memberName":"Nick",
"groupId":1,
"posts":[
{
"postId":1,
"postContent":""
},
{
"postId":2,
"postContent":""
}
]
},
{
"memberId":2,
"memberName":"John",
"groupId":1,
"posts":[
{
"postId":3,
"postContent":""
},
{
"postId":4,
"postContent":""
}
]
}
]
}
References
Advertisement
I am working on a web app called spliito.com that solves the tedious task of recording the money owed and borrowed that occurs when you go on a trip with friends in a spreadsheet and calculating who should pay back how much to whom at the end of the trip.
Posted on March 17, 2022
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related
August 14, 2024
