Support recursive relationships #3725

ridsameer · 2020-09-21T15:45:57Z

Problem

Let's say I have a table called categories. This table can refer to itself as a foreign key with parentCategory.
So a categories can have many subcategories, and subcategories can also have subcategories, with an unlimited combination. The idea is to support recursive queries to easily query them along with their sub categories.

Alternatives

N/A

Additional context

N/A

The text was updated successfully, but these errors were encountered:

sbrichardson · 2020-09-22T03:29:36Z

For data structures like this, The best way I've found to handle with GraphQl in general (unrelated to prisma) is to flatten the tree to one level, and track the parentId/index etc as properties. Then have a simple/fast recursive function that builds the tree or converts back to a flat list etc.

This is more of a Graphql limitation (not supporting recursion) but I've found working with a flattened list vs (n)tree has other benefits, like being able to mutate the tree as a linked list/doubly linked list etc).

pantharshit00 · 2020-09-22T04:15:39Z

Self referencing relations are possible in the Prisma schema today. See docs on them here: https://www.prisma.io/docs/reference/tools-and-interfaces/prisma-schema/relations#self-relations

That can get you started. But in general thanks for opening this issue for WITH RECURSIVE CTEs. This can improve experience here.

For now you can also use a raw query here: https://www.prisma.io/docs/reference/tools-and-interfaces/prisma-client/raw-database-access

ridsameer · 2020-09-29T05:58:07Z

Thanks @pantharshit00 . Thankfully this isn't required in my project right now but is in a roadmap for another personal project so thought I'd bring it up.

Thanks again

masihjahangiri · 2021-08-01T12:37:20Z

I have used raw queries for recursive queries.
I Think It's simple to implement in Prisma and it's very common in every project.

danielolaviobr · 2021-08-06T06:11:57Z

Couldn't a possible solution be having a decorator in our model saying that a certain relation is included by default when we query that table? So that when we would query for the tree structure, we would only query for the rows that have the parentId as null and the children would come by default, even in the children's children.

lumenwrites · 2021-11-25T15:47:08Z

Hey guys, I'm struggling with the same issue, and would really like to see this feature. Someone on StackOverflow suggested that I leave a comment here to help the developers track the demand for this feature.

Meanwhile, can someone please help me to figure out how to write a raw query that accomplishes the same thing? I'm not too great at SQL, and I'm relatively new to Prisma, and I'd really love to find a solution to this.

brielov · 2021-11-30T23:48:53Z

I've been waiting for this feature too. It would be awesome to see this feature gain some traction.

Southclaws · 2022-02-17T10:08:00Z

If the Prisma team is looking for a real-world example then I have one!

Here's a schema with a Post model which has repies and also a root post. The reason it's done this way is so there doesn't need to be a separate model for "threads" and "posts" it's all just a "post" and "first" indicates if it's the first post in a thread.

And here's a recursive CTE used to count the number of replies to threads (posts with "first" set to true)

If this could be expressed purely via Prisma, that would be awesome! I already hate the complexity and lack of composability with SQL syntax, but recursive CTEs are a special kind of hell!

JaapWeijland · 2022-03-15T14:04:57Z

+1

sbrichardson · 2022-03-25T18:13:21Z

For data structures like this, The best way I've found to handle with GraphQl in general (unrelated to prisma) is to flatten the tree to one level, and track the parentId/index etc as properties. Then have a simple/fast recursive function that builds the tree or converts back to a flat list etc.

This is more of a Graphql limitation (not supporting recursion) but I've found working with a flattened list vs (n)tree has other benefits, like being able to mutate the tree as a linked list/doubly linked list etc).

This is definitely the best way to handle, in my opinion. You can refold the items on the client or server with a simple recursive for loop.

This (incomplete?) example may have some unnecessary steps for your need, but it's taken from code that takes an ordered, flat array of React/DOM components (json) and builds a correct DOM/component tree using a parent id stored on each component. It could also be adapted to track an index property, if the array needed to be stored unordered.

const children = []
const treeDict = {}


const childrenNested = generateTree(children, treeDict)


function generateTree(ch, dict) {
  const tree = []
  for (
    let i = 0, children, parent, node, pid, id, n = ch.length;
    i < n;
    ++i
  ) {
    node = ch[i]
    id = node.id
    pid = node.pid
    if (pid === null || !(typeof pid === 'string' && pid.length > 0)) {
      tree[tree.length] = dict[id]
      continue
    }
    parent = dict[pid]
    children = parent.children
    if (children === null || children === undefined) {
      parent.children = [dict[id]]
      continue
    }
    children[children.length] = dict[id]
  }
  return tree
}

Jackman3005 · 2022-07-13T02:02:21Z

We also have recursive relationships in our database, similar to nodes having potentially one parent and potentially many children. As we grow features to process data across nodes at depth it's becoming a performance hit to use Prisma because we need to continuously query, check for more nodes and query each of those again, etc.

Is there any plan for this to be on the roadmap at any point in the next 6 months?

Thanks in advance!

nac62116 · 2022-09-07T11:19:00Z

+1

andrewmclagan · 2022-09-12T02:28:11Z

Bumping this as the issue was opened a year ago. For what is a pretty basic feature I would expect more traction from the Prisma team?

SooOverpowered · 2022-09-17T16:04:33Z

Faced similar situation like this. Ended up writing a helper function just to build up the category object. Probably wasting performance on querying but it solves the problem.

Here is the sample code. The data structure goes as follow: Project -> Category -> Subcategory -> subsub... you get the gist of it. The include: {document: true} is just additional data that I want to include for the query.

import prisma from '../../../lib/prisma'

async function GetSubCategory(arr) {
    const new_arr = []
    for (const element of arr) {
        const subCategories = await prisma.category.findMany({
            where: {
                parentCategoryId: element.id
            },
            include: {
                documents: true
            }
        })
        if (!Array.isArray(subCategories) || !subCategories.length) {
            // array does not exist, is not an array, or is empty
            // ⇒ do not attempt to process array
            new_arr.push(element)
        } else {
            const temp_arr = await GetSubCategory(subCategories)
            element.subCategories = temp_arr
            new_arr.push(element)
        }
    }
    return new_arr
}

export default async function handler(req, res) {
    const session = await unstable_getServerSession(req, res, authOptions)
    const project = await prisma.user.findUnique({
        where: {
            email: session.user.email
        }
    }).projects({
        where: {
            id: req.query.pid
        },
        include: {
            categories: {
                include: {
                    documents: true,
                }
            }
        }
    })
    project[0].categories = await GetSubCategory(project[0].categories)

    if (!project[0]) {
        res.status(404).json({ message: "Project not found." });
        return;
    }
    return res.json(project[0])
}

suraj5969 · 2023-01-10T12:11:02Z

+1

Alex-ray · 2023-01-13T04:31:04Z

+1

I'm surprised recursive querying for self referencing models aren't support for Prisma. Even simple features like nested folders is commonly expected for modern applications. Given there is underlying DB support for this, I'm curious why this hasn't been prioritized?

abedaarabi · 2023-01-16T07:28:51Z

I tried to use your scheme as an example, but unfortunately I got an error: :/

Error validating: A self-relation must have onDelete and onUpdate referential actions set to NoAction in one of the @relation attributes. (Implicit default onDelete: SetNull, and onUpdate: Cascade) Read more at https://pris.ly/d/cyclic-referential-actions

I needed up doing it that why: ofc i can't query the depth of each subfolder :/

model Folder {
id String @id @unique @default(uuid())
createdAt DateTime @default(now())

name String
isFolder Boolean @default(true)
childId String
}

stepaniukm · 2023-03-15T21:31:19Z

+1 Waiting for this!

jonknyc · 2023-04-01T10:12:31Z

You can use a recursive function

private recursive(level: number) {
    if (level === 0) {
      return {
        include: {
         categories: true
        }
      };
    }
    return {
      include: {
        categories: this.recursive(level - 1)
      }
    };
  }

after call it at

this.prisma.category.findUnique({
   where: {id},
   include: {
       categories: this.recursive(10),
    }
});

However, this will create a recursion with a known number of floors

I would propose something like a special "recurse" option on the include interface. Ideally it could be "true" for infinite recursion, or a number if I want to specify a max recursion depth.

For example:

Infinite recursion:

this.prisma.category.findUnique({
  where: {id},
  include: {
    categories: {
      recurse: true // recurse infinitely
    }
  }
})

Max depth:

this.prisma.category.findUnique({
  where: {id},
  include: {
    categories: {
      recurse: 10 // max of 10 levels deep
    }
  }
})

This feels like it shouldn't be too hard to create, and it would be such a great feature.

antoine13330 · 2023-05-09T16:20:51Z

Any Info about a potential release of the feature ? Kind of needed and a big time saver too. One of the main reason to use ORM too 🤔 ( however everything is incredible down here thanks a lot prisma team ❤️ )

jedwards1211 · 2023-05-15T14:43:10Z

@sbrichardson

For data structures like this, The best way I've found to handle with GraphQl in general (unrelated to prisma) is to flatten the tree to one level, and track the parentId/index etc as properties. Then have a simple/fast recursive function that builds the tree or converts back to a flat list etc.

This is orthogonal to the need for recursive queries...you'll get flat output from the database and reconstruct the tree in JS regardless, but if the table is large you can't afford to fetch everything, you have to select only the rows you want and then use a recursive query to fetch their ancestors and/or descendants, and the tree-building function doesn't help with that

AlexanderHott · 2023-06-19T19:37:22Z

Closing in on 3 years, is this on any roadmap?

Umbrien · 2023-06-19T20:19:11Z

Closing in on 3 years, is this on any roadmap?

@AlexanderHott here is Prisma roadmap and sadly there is no mention of this feature as long as no feedback from the team here too

TamirCode · 2023-06-23T00:03:54Z

I also have the posts -> comments -> comments use case. But another use case I have is for the permissions system I am building. Each roles will have a set of permissions, but they can also inherit permissions of other roles, like in this example (imagine foreign keys instead of strings):

const roles = [
	{
		name: "user",
		permissions: ["services.read"]
	},
	{
		name: "moderator",
		permissions: ["services.create"],
		inherit: ["user"]
	},
	{
		name: "admin",
		permissions: ["services.edit", "services.delete"],
		inherit: ["moderator"]
	},
]

If a user has role admin, I would need a recursive query and then i will gather all his permissions with javascript

// prisma returned object:
const exampleUser = {
	username: "blah",
	permissions: ["random.read", "random.create", "random.edit"], // specific permissions to user
	permissionsDenied: ["services.delete"], // negate permissions (highest precedence)
	roles: [
		{
			name: "admin",
			permissions: ["services.edit", "services.delete"],
			inherit: [
				{
					name: "moderator",
					permissions: ["services.create"],
					inherit: [
						{
							name: "user",
							permissions: ["services.read"]
						}
					]
				}
			]
		}
	]
}

and hopefully it would take care of edge cases like having circular reference would stop the recursion

TamirCode · 2023-06-23T00:34:25Z

possible syntax for above

const user = await prisma.user.findUnique({
	where: {
		id: 99,
	},
	include: {
		roles: {
			recursiveInclude: { 
				inherit: true
			}
			// ^ same as include { inherit: { include: inherit ...}}
		}
	}
})

mbilaldnc · 2023-07-12T15:37:51Z

Dear Prisma developers, I don't like writing comments like "+1" but please do not ignore this feature request. I've been searching this topic occasionally for about a year incase I missed an issue related to it.

rafaelklaessen · 2023-08-02T23:32:05Z

Are there any updates on this? The proposed recurse: true syntax seems great -- has this issue been added to a roadmap?

Jackman3005 · 2023-08-02T23:40:47Z

For us we not only would want to select/include relationships recursively, but would need the ability to do wheres recursively as well. In our model, you may be assigned to a "Quest". A Quest can have "SubQuests" which creates the recursion. If you are assigned to the top Quest, you should be considered assigned to all subquests below. When checking which permissions a user has for a particular subquest, we need to be able to recurse through all Quests above it until reaching the root to determine if they have been assigned or not.

We are hacking this with a hard-coded "depth" parameter for now, but it's not very performant and breaks when the depth goes beyond that.

1216892614 · 2023-08-25T05:59:04Z

Will this been add in Roadmap? Please do not ignore this feature request!

janpio · 2023-08-25T09:57:48Z

Yes, one day. Right now we are busy with other things. We only have limited capacity, so can not just add all the things we find useful to a roadmap - otherwise none of them will get done.

Neosoulink · 2024-02-27T04:41:41Z

Nah... We need this recurse: true 😅

antoine13330 · 2024-02-27T10:23:04Z

Nah... We need this recurse: true 😅

Yes that's kinda of shame that we don't have it...

justin-hackin · 2024-03-16T16:05:26Z

You can use a recursive function

private recursive(level: number) {
    if (level === 0) {
      return {
        include: {
         categories: true
        }
      };
    }
    return {
      include: {
        categories: this.recursive(level - 1)
      }
    };
  }

after call it at

this.prisma.category.findUnique({
   where: {id},
   include: {
       categories: this.recursive(10),
    }
});

However, this will create a recursion with a known number of floors

As I suspected, this messes up the TS types for the model but I used it to generate the unwieldy object for the query.

rafaelklaessen · 2024-03-16T16:09:56Z

You can use a recursive function
private recursive(level: number) {
    if (level === 0) {
      return {
        include: {
         categories: true
        }
      };
    }
    return {
      include: {
        categories: this.recursive(level - 1)
      }
    };
  }
after call it at
this.prisma.category.findUnique({
   where: {id},
   include: {
       categories: this.recursive(10),
    }
});
However, this will create a recursion with a known number of floors
As I suspected, this messes up the TS types for the model but I used it to generate the unwieldy object for the query.

Wouldn't you be better of simply getting all categories and building the hierarchical structure post-retrieval? Would be more performant, and you could have a variable number of nesting levels

Neosoulink · 2024-03-18T20:50:01Z

Effectively, another challenge when using Prisma helpers is to keep your Typescript able to follow your logic

On my side I created a logic able to do that, to create a recursive function and keep my typing updated

Here is my solution:

export const getVotes = (auth?: SignedInAuthObject | SignedOutAuthObject) => {
  return {
    ...(auth?.userId
      ? {
          upVotes: { where: { userId: auth.userId } },
          downVotes: { where: { userId: auth.userId } },
        }
      : {}),
    _count: { select: { upVotes: true, downVotes: true } },
  };
};

// ...

// I created a recursive function and defined the return type

export interface RecursivePost extends ReturnType<typeof getVotes> {
  comments?: {
    orderBy: { createdAt: "desc" | "asc" };
    include: RecursivePost | ReturnType<typeof getVotes>;
  };
}

export const generateNestedComments = (
  depth: number,
  auth?: SignedInAuthObject | SignedOutAuthObject,
): RecursivePost => {
  if (depth === 0) return getVotes(auth);

  const include = generateNestedComments(depth - 1, auth);

  return {
    ...getVotes(auth),
    comments: {
      orderBy: { createdAt: "desc" },
      include,
    },
  };
};

💡 In my shared code I'm getting a Post and a nested list of comments on that post, and because I was drunk and lazy I used the same Model for Post and Comment. But, the most important thing here is the fact that I'm able to set a depth to my function and keep my Typescript able to follow the logic.

Here are the code source links:

The repo: https://github.com/Neosoulink/reddit-clone/blob/dev
The helper location: https://github.com/Neosoulink/reddit-clone/blob/dev/src/lib/server-utils.ts#L47
And the integration of the helper: https://github.com/Neosoulink/reddit-clone/blob/dev/src/server/api/routers/post.ts#L65

Agreed, my code is not the best one and it would be much easier with a built-in solution, but still, my solution helped me with this Reddit-clone app, (here's the link https://reddit-clone-nine-xi.vercel.app/)

justin-hackin · 2024-04-15T16:37:48Z

You can use a recursive function
private recursive(level: number) {
    if (level === 0) {
      return {
        include: {
         categories: true
        }
      };
    }
    return {
      include: {
        categories: this.recursive(level - 1)
      }
    };
  }
after call it at
this.prisma.category.findUnique({
   where: {id},
   include: {
       categories: this.recursive(10),
    }
});
However, this will create a recursion with a known number of floors
As I suspected, this messes up the TS types for the model but I used it to generate the unwieldy object for the query.
Wouldn't you be better of simply getting all categories and building the hierarchical structure post-retrieval? Would be more performant, and you could have a variable number of nesting levels

Getting all the records is not expensive if say, there's a global category tree and all users need the whole tree. However, it might be more complicated than that and you could end up with lots of irrelevant categories.

This gave me an 💡: you can limit access to records with Zenstack so that you only get the relevant categories. This could be really useful if say a category could exist in multiple trees via a many-to-many relationship. However, Zenstack doesn't work with recursion either so I ended up with some ugly rules to deal with recursion e.g. :

@@allow('all',
    regions?[admins?[user == auth()]] ||
    parentCalendarGroup.regions?[admins?[user == auth()]] ||
    parentCalendarGroup.parentCalendarGroup.regions?[admins?[user == auth()]] ||
    parentCalendarGroup.parentCalendarGroup.parentCalendarGroup.regions?[admins?[user == auth()]]
)

There's a somewhat related issue here: zenstackhq/zenstack#276

Aloysius999 · 2024-06-10T17:08:24Z

Is there an update to this feature request?
It's been nearly 4 years since the original post.
A recursive feature is required for my project - my example is a folder tree for a messaging system.
Thanks

saidbenmoumen · 2024-07-02T15:06:45Z

+1

antoine13330 · 2024-07-02T17:00:50Z

Is there an update to this feature request? It's been nearly 4 years since the original post. A recursive feature is required for my project - my example is a folder tree for a messaging system. Thanks

Idk if u made it but u can try to queryRaw and make the recursive query yourself if really needed.

see here

PS : saw after writing that people already said it but just pinging u trying to help :)

BUT PLEASE PRISMA TEAM DO IIIIIIITTTTTTT ( still love ya )

pantharshit00 added kind/feature A request for a new feature. team/product labels Sep 22, 2020

albertoperdomo mentioned this issue Oct 8, 2020

sql WITH clause #3643

Closed

janpio removed the team/product label Dec 11, 2020

pantharshit00 added the domain/client Issue in the "Client" domain: Prisma Client, Prisma Studio etc. label Mar 2, 2021

janpio added the topic: database-functionality label Jun 1, 2021

sorenbs mentioned this issue Jun 13, 2021

Tree structures support #4562

Open

brielov mentioned this issue Apr 5, 2022

Support for Polymorphic Associations #1644

Open

jzzfs mentioned this issue Aug 8, 2022

Recursive geo location models with known depth #14706

Open

timsuchanek mentioned this issue Mar 28, 2019

Introspecting the @id directive of a MongoDB prisma/prisma1#3733

Closed

This comment was marked as abuse.

Sign in to view

This comment was marked as off-topic.

Sign in to view

janpio added temp: FOOO and removed temp: FOOO labels Oct 13, 2023

keinsell mentioned this issue Apr 23, 2024

Aggregations keinsell/is-prisma-production-ready#5

Open

2 tasks

Support recursive relationships #3725

Support recursive relationships #3725

Comments

ridsameer commented Sep 21, 2020

Problem

Suggested solution

Alternatives

Additional context

sbrichardson commented Sep 22, 2020

pantharshit00 commented Sep 22, 2020

ridsameer commented Sep 29, 2020

masihjahangiri commented Aug 1, 2021 • edited by pantharshit00 Loading

danielolaviobr commented Aug 6, 2021

lumenwrites commented Nov 25, 2021 • edited Loading

brielov commented Nov 30, 2021

Southclaws commented Feb 17, 2022

JaapWeijland commented Mar 15, 2022

sbrichardson commented Mar 25, 2022 • edited Loading

Jackman3005 commented Jul 13, 2022

nac62116 commented Sep 7, 2022

andrewmclagan commented Sep 12, 2022

SooOverpowered commented Sep 17, 2022

suraj5969 commented Jan 10, 2023

Alex-ray commented Jan 13, 2023 • edited Loading

abedaarabi commented Jan 16, 2023

stepaniukm commented Mar 15, 2023

This comment was marked as abuse.

jonknyc commented Apr 1, 2023 • edited Loading

antoine13330 commented May 9, 2023

jedwards1211 commented May 15, 2023 • edited Loading

AlexanderHott commented Jun 19, 2023

Umbrien commented Jun 19, 2023

TamirCode commented Jun 23, 2023 • edited Loading

TamirCode commented Jun 23, 2023

mbilaldnc commented Jul 12, 2023

This comment was marked as off-topic.

rafaelklaessen commented Aug 2, 2023

Jackman3005 commented Aug 2, 2023

1216892614 commented Aug 25, 2023

janpio commented Aug 25, 2023

Neosoulink commented Feb 27, 2024

antoine13330 commented Feb 27, 2024

justin-hackin commented Mar 16, 2024

rafaelklaessen commented Mar 16, 2024

Neosoulink commented Mar 18, 2024

justin-hackin commented Apr 15, 2024 • edited Loading

Aloysius999 commented Jun 10, 2024

saidbenmoumen commented Jul 2, 2024

antoine13330 commented Jul 2, 2024 • edited Loading

masihjahangiri commented Aug 1, 2021 •

edited by pantharshit00

Loading

lumenwrites commented Nov 25, 2021 •

edited

Loading

sbrichardson commented Mar 25, 2022 •

edited

Loading

Alex-ray commented Jan 13, 2023 •

edited

Loading

jonknyc commented Apr 1, 2023 •

edited

Loading

jedwards1211 commented May 15, 2023 •

edited

Loading

TamirCode commented Jun 23, 2023 •

edited

Loading

justin-hackin commented Apr 15, 2024 •

edited

Loading

antoine13330 commented Jul 2, 2024 •

edited

Loading