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.

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

id

as a field, this wasn't possible.
This was a little bit confusing

but I got it working.

Thanks!

Jump to solution

Prisma•16mo ago•

3 replies

Jvwooo-er 👻

FindUnique/Update using unique index on sub field

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

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.

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

id

as a field, this wasn't possible.
This was a little bit confusing

but I got it working.

Thanks!

Jump to solution

FindUnique/Update using unique index on sub field

FindUnique/Update using unique index on sub field

Similar Threads

Similar Threads

Similar Threads