graphql query SQL parent child relationship

Question

I have a postgres table that represents a hierarchy with a parent child table:

Table (Categories):

id name parentId
1  CatA null
2  CatB null
3  CatC 1
4  CatD 1
5  CatE 3

desired result:

categories: 
[
   {
      name: "CatA",
      children: [
      {
         name: "CatC",
         children: [
         {
             name: "CatE",
             children: []
         }]
      },
      {
         name: "CatD",
         children: [] 
      } 
   ],

 },
 {
       name: "CatB",
       children: []
 }
]

The problem is that I don't know how many levels there are, so I can't query something like:

category {
  name
  parent {
    name
    parent {
       name
       ...

LordDave · Answer 1 · 2016-08-09T22:05:04.523

You can actually achieve the potential infinite recursion with GraphQL. So it doesn't mind if you don't know how deep you go with your schema.

I was able to reproduce your desired result with this schema. I hope it might helps you:

const categories = [
    {
        name: 'CatA',
        children: [
            {
                name: 'CatC',
                children: [
                    {
                        name: 'CatE',
                        children: []
                    }]
            },
            {
                name: 'CatD',
                children: []
            }
        ]
    },
    {
        name: 'CatB',
        children: []
    }
];

const categoryType = new GraphQLObjectType({
    name: 'CategoryType',
    fields: () => ({
        name: { type: GraphQLString },
        children: { type: new GraphQLList(categoryType) }
    })
});

const queryType = new GraphQLObjectType({
    name: 'RootQuery',
    fields: () => ({
        categories: {
            type: new GraphQLList(categoryType),
            resolve: () => categories
        }
    })
});

And I got this result:

Please notice that I define field property as a function rather than an plain object. The field property defined as object would failed and wouldn't allow you to use categoryType variable in the fields, because in the time of execution it doesn't exist.

fields: () => ({
   ...
})

Hi, this approach needs to be using a query. What if the user doesn't know how depth is the child. My question is can is this can be achieve automatically looping through the children? — XxXk5XxX, Jan 23 '17 at 08:29

Andy Carlson · Answer 2 · 2016-12-20T19:25:55.680

One of the difficulties of using GraphQL on top of a SQL database is reconciling the two paradigms. GraphQL is hierarchical. SQL databases are relational. There isn't always a clear mapping between the two.

We open-sourced a framework, Join Monster, that has an opinionated way of setting up your schemas. If you do so, it automatically generates the SQL queries for you. It was built with the idea of relations in its core. In theory you can achieve arbitrary depth in your GraphQL queries.

graphql query SQL parent child relationship

2 Answers2

Linked