Prisma: Further JOIN on JOINed records
Tags: prisma, nestjs
Published: 2022-03-17 08:15:42

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.
```javascript
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.
```javascript
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.
```json
{
   "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
[https://www.prisma.io/docs/concepts/components/prisma-client/relation-queries#include-deeply-nested-relations](https://www.prisma.io/docs/concepts/components/prisma-client/relation-queries#include-deeply-nested-relations)

## Advertisement
I am working on a web app called [spliito.com](https://bit.ly/3H7w3Zf) 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.
![screen](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tzzttal5w02ljb2u2nmt.png)