PrismaP
Prisma•14mo ago
Jvwooo-er 👻

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
  ...
}

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 } } },
});

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.

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
  ...
}


Now I can use it as
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!
Jump to solution
Was this page helpful?