How to search M to N relationship in prisma?

731 views Asked by At

I'm working on project with prisma nowadays. I designed m to n relationship table. Below is my code. schema.prisma

model Artists {
  id                 Int                  @id @default(autoincrement())
  artistName         String?
  Albums             Albums[]
  Artists_Tracks     Artists_Tracks[]
}

model Artists_Tracks {
  id       Int      @id @default(autoincrement())
  trackId  Int?
  artistId Int?
  Artists  Artists? @relation(fields: [artistId], references: [id])
  Tracks   Tracks?  @relation(fields: [trackId], references: [id])

  @@index([artistId], name: "Artists_Tracks_artistId_foreign_idx")
  @@index([trackId], name: "Artists_Tracks_trackId_foreign_idx")
}

model Tracks {
  id                Int                 @id @default(autoincrement())
  trackName         String?
  albumTrackNumber  Int?
  albumId           Int?
  Albums            Albums?             @relation(fields: [albumId], references: [id])
  Artists_Tracks    Artists_Tracks[]

  @@index([albumId], name: "Tracks_albumId_foreign_idx")
}

This is my prisma code. What I want to do is search by trackname and get all tracks's information with artist's name.

+edit) What I've tried is

// first try
optObj.include = {
    Albums: {
      select: { cover: true },
    },
    Artists_Tracks: {
       Albums: true
    },
 };

// second try
optObj.include = {
    Albums: {
      select: { cover: true },
    },
    Artists_Tracks: true,
    Artists: true,
  };


  const result = await prisma.tracks.findMany(optObj);

and use promise.all to get artist's name for each track. Is there any way to do that in once by one query? I'm pretty sure there would be more better way to do this. Thank you in advance.

1

There are 1 answers

2
Ryan On BEST ANSWER

As per your schema, the include will not contain the Artist relation. It will have to be included from the Artists_Tracks relation like this:

const result = await prisma.tracks.findMany({
    include: {
      Albums: {
        select: { cover: true },
      },
      Artists_Tracks: { include: { Artists: true } },
    },
})

This way you can get the Artist for each track present.