Prisma: Further JOIN on JOINed records

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

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 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.