Hey all. I'm currently having a few difficulties with setting up a relation between two tables in Postgres Prisma client. The following queries are to find the giveaway which the user is trying to enter, and then trying to check if that user has already entered said giveaway. The

giveawayId

in the

giveawayEntry

table is a foreign key to the

id

field in the

giveaway

table. I'm not 100% confident in my schema setup - which could be the problem.

const findGiveaway = await client.prisma.giveaway.findFirst({
    where: {
      messageId: interaction.message.id
    }
  })

  const findUser = await client.prisma.giveawayEntry.findFirst({
    where: {
      userId: interaction.user.id,
      giveawayId: findGiveaway.id,
    }
  })

model Giveaway {
  id                      Int            @id @default(autoincrement())
  messageId               String         @db.VarChar(20)
  GiveawayEntry           GiveawayEntry?
}

model GiveawayEntry {
  id           Int      @id @default(autoincrement())
  userId       String   @db.VarChar(20)
  giveawayId   Giveaway @relation(fields: [id], references: [id])
}

The error is appearing on the line giveawayId: findGiveaway.id, in the second query to check if the user has already entered the giveaway.

Type 'number' is not assignable to type '(Without<GiveawayRelationFilter, GiveawayWhereInput> & GiveawayWhereInput) | (Without<GiveawayWhereInput, GiveawayRelationFilter> & GiveawayRelationFilter) | undefined'

Hey Logan 👋 You need to run the findUser query like this:

const findUser = await client.prisma.giveawayEntry.findFirst({
    where: {
      userId: interaction.user.id,
      giveawayId: {
        id: findGiveaway.id
      },
    }
  })

Brilliant, thanks, this has resolved the problem. Another quick question. If I wanted to setup cascading deletes whenever a giveaway has been deleted inside of the

giveaway

table and then delete all rows in the

giveawayEntry

table when this happens, how could I set this up? Is there anything special you have to do with the delete query, if prisma schema? Or does it do it automagically when you have setup a foreign key between two tables?

For that you would need to add Referential Action of

OnDelete

to

Cascade

Your schema could look something like this:

model Giveaway {
  id            Int            @id @default(autoincrement())
  messageId     String         @db.VarChar(20)
  GiveawayEntry GiveawayEntry?
}

model GiveawayEntry {
  id         Int      @id @default(autoincrement())
  userId     String   @db.VarChar(20)
  giveawayId Giveaway @relation(fields: [id], references: [id], onDelete: Cascade)
}

Thanks! a bunch 🙂

I am trying to understand your use case, do you want to create a Giveaway and GiveawayEntry record both in a single create query?

No, not quite. What I'm trying to do is when a user enters a giveaway I want to add their entry into the

giveawayEntry

table, the entry should have a link to the

giveaway

table and the coresponding giveaway which has already been created. For example: giveaway table:

id: giveawayId
prize: prize

When a user enters this giveaway (It has to already have been made by someone else for them to enter, and many different people can enter the same giveaway) I will add them to the

giveawayEntry

table which will have a link to this giveaway via a foreign key giveawayEntry table:

id: entryId
giveawayId: giveawayId
userId: userId

the

giveawayId

field in the

giveawayEntry

table is a link to the

id

field in the

giveaway

table Users can enter multiple giveaways at any given time, and there can be multiple giveaways active at any time. Hopefully this makes a bit more sense? Sorry if I have explained it badly.