8base GraphQL API Recursive SQL Queries Support

Feature Ideas
#1

Hello,

Our app is going to consist of alot of hierarchical data. I’m wandering if there’s any way to perform a recursive query using the API as you would in MySQL such as:

with recursive cte (id, name, parent_id) as (
  select     id,
             name,
             parent_id
  from       products
  where      parent_id = 19
  union all
  select     p.id,
             p.name,
             p.parent_id
  from       products p
  inner join cte
          on p.parent_id = cte.id
)
select * from cte;

Concerned that that the only alternative would be to do a for… await and simply recursively query the API until we reach the node we want.

Thanks

#2

Hey @nce.softdev ! The 8base GraphQL API doesn’t have a recursive function per your example. You have to explicitly state the depth of the query. For example, if you had a table called Person that had Parents and Children (records), you’d get 4 levels of depth by:

query {
  person(id: $pid) {
    ...fields
    child {
      ...fields
      child {
        ...fields
        child {
          ...fields
        }
      }
    }
  }
}

That said, one option you have is to create a custom resolver function that handles the recursive API call on the server side, and the exposes the full hierarchy through a more simple API interface. For example, you us an API call like the one above inside the custom function, and then expose it with an operation like:

query {
  familyTreeResolver(personId: $pid) {
    ...fields
  }
}
#3

Yeah thanks - for anyone with a similar issue, the solution we found was to use another model for all the nodes in a particular graph to relate to. In that sense, you can query all the nodes for the graph from a single model instance, and then build the graph using a recursive mapping function of some kind.

1 Like
#4

Love it! Glad you found a solution that works for you. Please drop in here too what the “ideal solution” would have been too, if you think the API should have natively handled it.

#5

Apologies for late reply -

Yeah I think there is a decent case for 8base handling recursive queries natively - simply because it is a native feature of many SQL databases, whilst being on the more complex end.

So a recursive query is one which performs a filter function recursively against a set of results until there are none left. It would work similar to the current filter function, however it would then re-apply this this same filter to the result set.

Ie, if you had a ‘recursiveQuery’ argument in a list query, it could look something like:

query {
    blogsList(recursiveQuery: {
        recursiveResultName: 'previousSet'
        filter: {
            relatedBlogs: {
                some: {
                    id: {
                        in: previousSet
                    }
                }
            }
        }
    }) {
        items {
            id
        }
        count
    }
}

Recursive queries I think could only really occur on tables which have a relation with themselves. In the above example, the Blog table has a field called relatedBlogs, which contains a list of blogs related to the current Blog. A filter method is performed to find all blogs which have a relation with a previous set of blogs, when this new list is found, it then becomes the ‘previousSet’ variable, and a new list of blogs will be returned. This goes on until the filter output equals zero, and the full list of blogs, ie every blog in the previousSet variables is concatenated into a single list, is returned to the user.
The project I’m working on makes alot of use of graph data modelling and so this would be a big help.

#6

Totally agree with this being relevant to self referential relationships. I think a really solid use case is in a system that enables Comments. Where Comments can have many replies, which are Comments… and so on. Just like Reddit does.

Thanks for bringing it up. Going to book mark it and bring it to the attention of our team.

@timothy.myers @estebanf