How to write recursive query for nested document collection

Hello everyone. I recently started using Fauna and I have been loving it. Although I have a small challenge. I have been stuck on this for 48 hours after trying to solve the problem.

The Problem
I have a comments collection. A comment has “replies” field (it stores replies to a comment. I use an array to store the ref in this field) and this creates nested comments. How do I recursively map an array if the “replies” field is not empty?

PS: My solution returns Maximum call stack size exceeded :face_with_peeking_eye:

Unlike relational databases, where every query can assume that it receives all of the database server’s resources to satisfy a query (assuming that’s possible), Fauna is a shared database service. While you are executing your queries, many thousands of other applications are also querying Fauna. To make that work, Fauna has to impose resource limits to mitigate the “noisy neighbor” effect.

Fauna doesn’t let you perform full table scans, except for very small collections. So you have to Paginate sets, and potentially have to execute multiple queries to get the full list of documents in the set.

You might have to restructure your data to make this work effectively. If you assume that the parent item is a post, each comment can simply have a post: Ref(Collection("posts"), "<post document id>") field. If you create an index that lets you search by post reference, then you can Paginate(Match("comments_by_post_ref"), <post_reference>)).

If there are more comments than fit on a single page, the response includes an after cursor, which lets you execute another query to fetch the next page of results. You can adjust the page size up to 100,000 results (the default is 64).

Hi Ewan, thank you for your response. After some research on how to do nested replies generally, this is the solution I came up with and it works. I created a recursive UDF and called it on the FQL query.

        replies: Map(
                id: Select(["ref", "id"], Get(Var("x"))),
                authorID: Select(['data', 'userID', 'id'], Get(Var('x'))),
                author: Select(["data", "username"], Get(Match(Index('getUserByID'), Var("authorID")))),
                content: Select(["data", "content"], Get(Var("x"))),
                repliedToCommentID: Select(
                  ["data", "repliedToCommentID", "id"],
                created_at: Select(['data', 'created'], Get(Var('x'))),
                replies: Call("LoopArray", [
                  Select(["data", "replies"], Get(Var("x")), [])
                ts: Select(['ts'], Get(Var('x'))),
                commentID: Var("id"),
                owner: Var('author'),
                ownerID: Var('authorID'),
                content: Var("content"),
                repliedToCommentID: Var("repliedToCommentID"),
                replies: Var("replies"),
                created: ToMillis(Var('created_at'))

PS: I used Paginate in the FQL query instead.

1 Like