Prisma: find where field is not set

Question

Say I have a schema like so:

model Person {
  id      String   @id @default(cuid())
  name    String
  email   String?
}

How could I find all persons named "foo" with no email? Basically, findMany or update based on name IS "foo" AND email IS NOT SET? I find the doc on the subject very confusing.

Could it be:

prisma.person.findMany({
  where: {
    name: "foo",
    email: undefined,
  }
})

or:

prisma.person.findMany({
  where: {
    name: "foo",
    email: null,
  }
})

or:

prisma.person.findMany({
  where: {
    name: "foo",
    email: { is: null },
  }
})

or ... ???

---

Does it change anything if it's an optional relation?

model Person {
  id      String   @id @default(cuid())
  name    String
  pet     Pet?     @relation
}
model Pet {
  id      String   @id @default(cuid())
  name    String
  ownerId String?
  owner   Person?  @relation(fields: [ownerId], references: [id])
}

How can I find all persons named "foo" with no pet? Is it different from finding all pets named "foo" with no owner"?

Answer 1

How could I find all persons named "foo" with no email?

Correct answer is:

prisma.person.findMany({
  where: {
    name: "foo",
    email: null,
  }
})

---

prisma.person.findMany({
  where: {
    name: "foo",
    email: undefined,
  }
})

Using undefined will equivalent to :

prisma.person.findMany({
  where: {
    name: "foo",
  }
})

So this basically find all person with name foo include who have email and do not have email, that's wrong.

---

prisma.person.findMany({
  where: {
    name: "foo",
    email: { is: null },
  }
})

Using is is only for relation query, so it's probably won't work.

---

How can I find all persons named "foo" with no pet?

prisma.person.findMany({
  where: {
    name: "foo",
    pet: { none: {} },
  }
})

---

Is it different from finding all pets named "foo" with no owner"?

I think it's the same

prisma.pet.findMany({
  where: {
    name: "foo",
    owner: { none: {} },
  }
})