Skip to content
Advertisement

Prisma update with relation in WHERE property

Given these schemas:

model awayinfo {
PlayerID   Int        @id
IsAway     Boolean
playerinfo playerinfo @relation(fields: [PlayerID], references: [ID])
}
    
model playerinfo {
name       String    @db.VarChar(15)
ID         Int       @id @unique @default(autoincrement())
awayinfo   awayinfo?
}

How would I create a prisma SQL update for the awayinfo table if the only identifier I have would be the Name of a player and not the ID?

what I tried:

I try to pass something into the WHERE part as seen below:

const result = await prisma.awayinfo.update({
    where: {
      PlayerID: {
        name: name
      }
  },
    data: {
      IsAway: true,
    }
  });

but it always gives me the Error:

Invalid `prisma.awayinfo.update()` invocation: 

Argument PlayerID: Got invalid value
{
  name: 'Dummy'

}
on prisma.updateOneawayinfo. Provided Json, expected Int.

I got pretty desperate and even tried wonky selects like this one

 const result = await prisma.awayinfo.update({
    where: {
      PlayerID: {
      playerinfo: {
        Where: {
        name: name
      },
      select: {ID: true},
    }}
  }, .....

but obviously this would not work aswell. I wonder what I am missing here and I cannot find any example of a condition within the WHERE clause in the prisma documentation

Advertisement

Answer

There are two issues here, the first is in your Prisma schema/data model and the second is in your query.

Making name unique

Firstly, if you want to uniquely identify a record in the playerinfo table with just the name property, this property must be unique. Otherwise, it’s not possible for your database (and Prisma) to know which player info record to update in case there are multiple records with the same name property.

Update your schema accordingly:

model playerinfo {
  name     String    @unique @db.VarChar(15)
  ID       Int       @id @unique @default(autoincrement())
  awayinfo awayinfo?
}

Rewriting the update query

Since the where condition in your update references a property playerinfo model, that is where you should begin your query. Here’s what it looks like:

const data = await prisma.playerinfo.update({
    where: {
        name: name
    }, 
    data: {
        awayinfo: {
            update: {
                IsAway: true
            }
        }
    }
})
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement