Prisma•3d ago

FindUnique/Update using unique index on sub field

Hi, I need some help with generating a schema where I can query indexed sub fields. I've looked for solutions but I couldn't really find a thread matching my problem. Let me sketch an example. (I'm working with Prisma ORM with MongoDB)

model Comment {
  id              String    @id @default(auto()) @map("_id") @db.ObjectId
  content         String
  replies         Reply[]
  ...

  @@unique([replies.id], map: "replies_id_1")
  @@map("comments")
}

type Reply {
  id      String @db.ObjectId
  content String
  ...
}

model Comment {
  id              String    @id @default(auto()) @map("_id") @db.ObjectId
  content         String
  replies         Reply[]
  ...

  @@unique([replies.id], map: "replies_id_1")
  @@map("comments")
}

type Reply {
  id      String @db.ObjectId
  content String
  ...
}

This schema will generate fine and put an index on _id and replies.id that works as intended (there can only be one reply on all existing comments with a specific id). I would like to use this unique index when querying the comments model. However there is no key/type for this to be passed as an argument for findUnique or update. the closest filter with the same result is this:

prisma.comment.findMany({
  where: { replies: { some: { id } } },
});

prisma.comment.findMany({
  where: { replies: { some: { id } } },
});

However this only works on ###Many operations. How can I add this 'self-made' index available for unique filtering?

Solution:

Hi Raphael, Thanks for your fast reply. I've tinkered with this some more and found out it actually can be done. However the subfield should have a name that is not inside the model. ...

Jump to solution

2 Replies

RaphaelEtim•3d ago

Hi @Jvwer Currently, there is no direct way to make this 'self-made' index available for unique filtering operations. We do have an open issue that discusses the possibility of allowing unique fields on related models to be used in queries, which is similar to your use case with nested fields.

GitHub

Support specifying transitive unique field when finding unique fiel...

Problem Consider: model User { uid Number @unique email String @unique } model ProfileValue { uid Number user User @relation(fields: [uid], references: [uid]) name String value String @@id([uid, na...

Solution

Jvwer•3d ago

Hi Raphael, Thanks for your fast reply. I've tinkered with this some more and found out it actually can be done. However the subfield should have a name that is not inside the model. so:

model Comment {
  id              String    @id @default(auto()) @map("_id") @db.ObjectId
  content         String
  replies         Reply
  ...

  @@unique([replies.replyId ], map: "replies_id_1")
  @@map("comments")
}

type Reply {
  replyId String @db.ObjectId @map("id")
  content String
  ...
}

model Comment {
  id              String    @id @default(auto()) @map("_id") @db.ObjectId
  content         String
  replies         Reply
  ...

  @@unique([replies.replyId ], map: "replies_id_1")
  @@map("comments")
}

type Reply {
  replyId String @db.ObjectId @map("id")
  content String
  ...
}

Now I can use it as

prisma.comment.findUnique({
  where: { replyId: "someId" },
});

prisma.comment.findUnique({
  where: { replyId: "someId" },
});

This is similar to how compound indexes work. Those have the same syntax: fieldName1_fieldName2. Because comment and reply both had id as a field, this wasn't possible. This was a little bit confusing 😅 but I got it working. Thanks!

Gaming

Programming

FindUnique/Update using unique index on sub field