CODEWITHSUNDEEP
sqldatabaserecursionrelationprisma
Hugo Bois
Hugo Bois

Reputation: 141

Prisma nested recursive relations depth

I must query a group and all of its subgroups from the same model.

However, when fetching from Group table as shown below, Prisma doesn't include more than a 1-depth to the resulting Subgroups relation (subgroups of subgroups being left out). Subgroups attribute holds an array whose elements are of same type as the said model (recursive).

model Group {
  id                Int         @id @default(autoincrement())
  parentId          Int?
  Parent            Group?      @relation("parentId", fields: [parentId], references: [id])
  Subgroups         Group[]     @relation("parentId")
}

GroupModel.findFirst({
  where:   { id: _id },
  include: { Subgroups: true }
});

I guess this might be some sort of safeguard to avoid infinite recursive models when generating results. Is there any way of dodging this limitation (if it's one), and if so, how?

Thanks

Upvotes: 3

Views: 3237

Answers (1)

Baptiste F.
Baptiste F.

Reputation: 139

You can query more than 1-depth nested subgroups by nesting include like so:

GroupModel.findFirst({
  where:   { id: _id },
  include: { Subgroups: { include: { Subgroups: { include: Subgroups: { // and so on...  } } } } }
});

But, as mentioned by @TasinIshmam, something like includeRecursive is not supported by Prisma at the moment.

The workaround would be to use $queryRaw (https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access#queryraw) together with SQL recursive queries (https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE)

Upvotes: 2

Related Questions