How to search M to N relationship in prisma?

Question

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.

score 2 · Accepted Answer · answered Dec 08 '20 at 12:51

2

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.

answered Dec 08 '20 at 12:51

Ryan

5,229
1
20
31

I tried this but I got errors :/ So I thought is there any special way to do in reverse way. include allows us to connect from many to one, but not one to many, isn't it? – lcpnine Dec 08 '20 at 12:58
1

I solved the problem as you said :/ it was because one of my teammate changed DBschema and didn't let us know bout it. It works perfect. Thank you so much. – lcpnine Dec 08 '20 at 15:41

How to search M to N relationship in prisma?

1 Answers1