P
Prisma•3w ago
Jvwer

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
RaphaelEtim•3w 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
Jvwer•3w 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!
Want results from more Discord servers?
Add your server