Can I do recursive / CTE query on convex
Can I do recursive / CTE query on convex?
I ask because I have a file system / document management system set up on convex, where docs are stored with a parentId this makes moves cheap as we build the hierarchy as we navigate but I want to implement metadata filtering based on path prefix, it would be great if there were CTEs available so that I could do a traversal/recursive query to build this automatically rather than all the machinery to maintain it myself. My use-case is full-text and vector search of documents under a given heirarchy path.
5 Replies
The short answer is "not directly", but most sql concepts can be translated to Convex one way or another. When you say filtering on path prefix, would that be a substring of a single text field or something else?
well path prefix would be like give me all documents under root/foldera/folderb where the solution is to store the full path on every document and do a range-query type thing on a prefix of the full path, now that still does not fully solve my problem because even if I can do that I now have to update every document in a subpath when I move a folder to update its path, I'd prefer for that value to be essentially a materialized view
and to create a query that given docs with just a parentId it generate the full path requres me to use a CTE / recursive query, this was a solved problem when I was on planetscale and since moving to convex I have not been sure how I want to solve it
so I guess I know the non CTE solutions I gues this is just my hope that I missed something and I perhaps could get this materialized view colum so that I can join to it and filter my full text and vector searches cleanly against it.
I think the mental blocker when comparing sql to convex ends up being an assumption that a TypeScript function with nested for loops or mapping functions could never match sql's performance. I've generally found that it's shockingly performant in these tests. My recommendation is to "just do things" in Convex and see what happens. In this case, do exactly what you're wanting to do - give each document a parent id. Then have your query take in a path prefix, parse the path, and grab every document for each path segment up to the root. You can add metadata as you go, too, eg., parent directory name or even the full path to the document as a strong on the object.
This is effectively building a materialized view on the fly, per function call. And it will probably be cached most of the time thereafter.
you are absoluetly right on that baked in intuition that TS !== SQL, but with convex you can just do things! I will learn to stop worrying and love the functions 😄
