Many to many relationships difficulties

[anderskitson]

I have read the many to many relationship doc here Prisma Relations and Redwood's Generators | RedwoodJS Docs

I have the following Schema

datasource db {
  provider = "postgres"
  url      = env("DATABASE_URL")
}

generator client {
  provider      = "prisma-client-js"
  binaryTargets = "native"
}

model User {
  id                Int                @id @default(autoincrement())
  uuid              String             @unique
  email             String             @unique
  name              String?
  userRoles         UserRole[]
  quests            Quest[]
  ambassadorProfile AmbassadorProfile?
}

model UserRole {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @default(now())
  name      String
  user      User?    @relation(fields: [userId], references: [id])
  userId    Int?
  password  String?

  @@unique([name, userId])
}

model Quest {
  id              Int                 @id @default(autoincrement())
  name            String?
  userId          Int
  submittedByuser User                @relation(fields: [userId], references: [id])
  heros           Hero[]
  ambassadors     AmbassadorProfile[]
}

model AmbassadorProfile {
  id           Int     @id @default(autoincrement())
  name         String?
  userId       Int     @unique
  questId      Int
  user         User    @relation(fields: [userId], references: [id])
  quests       Quest[]
  twitter      String
  profileImage String
}

model Hero {
  id      Int     @id @default(autoincrement())
  name    String?
  questId Int
  quests  Quest[]
  twitter String
}

I am primarily concerned right now with a Quest model have many Heros.

When I save a new Quest I also create a new Hero with the questId so I thought that would link the two, but it does not it just adds the id

const onSave = async (input) => {
    const castInput = {
      name: input.name,
      userId: currentUser.user.id,
    }
    const quest = await createQuest({ variables: { input: castInput } })
    const castInputHero = {
      name: input.heroName,
      questId: quest.data.createQuest.id,
      twitter: input.twitter,
    }
    await createHero({ variables: { input: castInputHero } })
  }

I have a relation table called _HeroToQuest and it has nothing in it. So not sure how to get it to create the relation so I can then query the heros on a Quest

<>
              {quests.map((quest) => (
                <div key={quest.id}>
                  <h3>{truncate(quest.name)}</h3>
                  {quest.heros.map((hero) => (
                    <div key={hero.id}>
                      <h4>{hero.name}</h4>
                      <p>{hero.twitter}</p>
                    </div>
                  ))}
                </div>
              ))}
            </>

quests.sdl.ts

export const schema = gql`
  type Quest {
    id: Int!
    name: String
    userId: Int!
    submittedByuser: User!
    heros: [Hero]!
    ambassadors: [AmbassadorProfile]!
  }

  type Query {
    quests: [Quest!]! @skipAuth
    quest(id: Int!): Quest @skipAuth
  }

  input CreateQuestInput {
    name: String
    userId: Int!
  }

  input UpdateQuestInput {
    name: String
    userId: Int
  }

  type Mutation {
    createQuest(input: CreateQuestInput!): Quest! @requireAuth
    updateQuest(id: Int!, input: UpdateQuestInput!): Quest! @requireAuth
    deleteQuest(id: Int!): Quest! @requireAuth
  }
`

quests.ts

import { db } from 'src/lib/db'
import type {
  QueryResolvers,
  MutationResolvers,
  QuestResolvers,
} from 'types/graphql'

export const quests: QueryResolvers['quests'] = () => {
  return db.quest.findMany()
}

export const quest: QueryResolvers['quest'] = ({ id }) => {
  return db.quest.findUnique({
    where: { id },
  })
}

export const createQuest: MutationResolvers['createQuest'] = ({ input }) => {
  return db.quest.create({
    data: input,
  })
}

export const updateQuest: MutationResolvers['updateQuest'] = ({
  id,
  input,
}) => {
  return db.quest.update({
    data: input,
    where: { id },
  })
}

export const deleteQuest: MutationResolvers['deleteQuest'] = ({ id }) => {
  return db.quest.delete({
    where: { id },
  })
}

export const Quest: QuestResolvers = {
  submittedByuser: (_obj, { root }) =>
    db.quest.findUnique({ where: { id: root.id } }).submittedByuser(),
  heros: (_obj, { root }) =>
    db.quest.findUnique({ where: { id: root.id } }).heros(),
  ambassadors: (_obj, { root }) =>
    db.quest.findUnique({ where: { id: root.id } }).ambassadors(),
}

I have both hero’s and quests but there are not related to eachother in the common relation table. Also Redwood has been a real joy to work with since 1.0. I find I can execute my ideas so much quicker and play around beyond the odd stumbling block like so.

[anderskitson]

Oh I think I need to redo my schema as explicit. I will try that, and regenerate my sdl’s I guess do I need to then generate a sdl for the HeroOnQuest model for the following schema change

datasource db {
  provider = "postgres"
  url      = env("DATABASE_URL")
}

generator client {
  provider      = "prisma-client-js"
  binaryTargets = "native"
}

model User {
  id                Int                @id @default(autoincrement())
  uuid              String             @unique
  email             String             @unique
  name              String?
  userRoles         UserRole[]
  quests            Quest[]
  ambassadorProfile AmbassadorProfile?
}

model UserRole {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @default(now())
  name      String
  user      User?    @relation(fields: [userId], references: [id])
  userId    Int?
  password  String?

  @@unique([name, userId])
}

model Quest {
  id              Int                 @id @default(autoincrement())
  name            String?
  userId          Int
  submittedByuser User                @relation(fields: [userId], references: [id])
  heros           HeroOnQuest[]
  ambassadors     AmbassadorProfile[]
}

model HeroOnQuest {
  id      Int   @id @default(autoincrement())
  heroId  Int
  hero    Hero  @relation(fields: [heroId], references: [id])
  questId Int
  quest   Quest @relation(fields: [questId], references: [id])
}

model AmbassadorProfile {
  id           Int     @id @default(autoincrement())
  name         String?
  userId       Int     @unique
  questId      Int
  user         User    @relation(fields: [userId], references: [id])
  quests       Quest[]
  twitter      String
  profileImage String
}

model Hero {
  id      Int           @id @default(autoincrement())
  name    String?
  questId Int
  quests  HeroOnQuest[]
  twitter String
}

Now I seem like I am stuck, after creating a Quest, Creating a Hero, and Creating a heroOnAQuest. I can’t query any of them with a relation

const onSave = async (input) => {
    const castInput = {
      name: input.name,
      userId: currentUser.user.id,
    }
    const quest = await createQuest({ variables: { input: castInput } })
    const castInputHero = {
      name: input.heroName,
      questId: quest.data.createQuest.id,
      twitter: input.twitter,
    }
    const hero = await createHero({ variables: { input: castInputHero } })
    const castInputHeroOnQuest = {
      questId: quest.data.createQuest.id,
      heroId: hero.data.createHero.id,
    }
    await createHeroOnQuest({ variables: { input: castInputHeroOnQuest } })
  }

query FindHeroes {
  heroes{
    quests{
      quest{
        name
      }
    }
  }
}

query FindQuests {
  quests{
    heros{
      hero{
        id
      }
    }
  }
}

query FindQuests2{
  heroOnQuests{
    id
    quest{
      name
    }
  }
}

all these queries return no data when using a relation

[anderskitson]

Was able to get it by creating an implicit. Then updating the hero with a connection like the following after reading this thread

Subqueries in Prisma, Many-Many relation

For the one many-to-many relationship I have, I just decided to let Prisma implicitly create the join table: An article can have many tweets (if some tweets linked an article in it) and a Tweet can have many articles( if multiple ones mentioned). model Article { id String @id @default(cuid()) createdAt DateTime @default(now()) updatedAt DateTime @default(now()) publishedAt DateTime @default(now()) entry Entry @…

export const updateHero: MutationResolvers['updateHero'] = ({ id, input }) => {
  return db.hero.update({
    where: { id: id },
    data: { quests: { connect: [{ id: input.questId }] } },
  })
}