Hi, I am wondering how to implement filtering with self reference tables. Let’s say you have User model which has Many to many self relations like below.

model User {
  id         Int       @id @default(autoincrement())
  name       String?
  children   FamilyRelation[] @relation("child")
  parents    FamilyRelation[] @relation("parent")
}

model FamilyRelation {
  child    User @relation("child", fields: [childId], references: [id])
  childId  Int
  parent   User @relation("parent", fields: [parentId], references: [id])
  parentId Int

  @@id([parentId, childId])
}

Now I want to query users who has ancestors named “John”.

user
id | name   | parent_user_id | child_user_id
1  | "John" | []             | [2, 4]
2  | "A"    | [1]            | []
3  | "B"    | [4]            | []
4  | "C"    | [1]            | [4]
5  | "D"    | []             | []

→ query result should be "A", "B", "C".

Keen to see what people say. one way would be to just get all with the parent connection and use filter to map those out? Do you know the ID of John at this point. Guessing you could use where parent_used_id = 1

Do you know the ID of John at this point.

No, because I imagine that filtering should be done not only by exact match but also

contain

,

startWith

and so on.

Guessing you could use where parent_used_id = 1

Even if id is specified before query, is it possible to query “B” ? “B” does not have “John” as a parent. “John” is grand parent from “B”.

Right, that gets a bit tricky indeed. Hmm that almost sounds like only recursive will be able to fix it. Would still think

filter

is a good way to go though