Warm tip: This article is reproduced from serverfault.com, please click

How to search M to N relationship in prisma?

发布于 2020-12-08 12:30:18

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.

Questioner
YuTaek
Viewed
0
Ryan 2020-12-08 20:51:51

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.