Recursive/Nested/Tree Data fetching in hasura

Question

I have user table which have foreign key to org-unit table (1-1) relation i.e. 1 user belong to 1 org-unit.

I have org-unit table which have column parent-id(self-reference) i.e. org-unit can be nested. e.g. orgUnit_1 with parent-id null, orgUnit_2 with parent-id 1, orgUnit_3 with parent-id 2.

orgUnit_1
    - orgUnit_2
        -orgUnit_3

user1 belongs to orgUnit_1
user2 belongs to orgUnit_2
user3 belongs to orgUnit_3

Now I wish, that when I fetch users from orgUnit_1, it should also bring its child orgUnit user as well.. i.e. user1, user2 and user3.

Is this possible, in hasura?

Answer 1

Recursive queries are not supported by GraphQL, but its possible to use recursive common table expressions in PostgreSQL. This can be defined as a function and used as table computed field:

drop function if exists organization_descendant_users(organization);
drop table if exists user_info;
drop table if exists organization;

create table organization (
    id serial primary key,
    parent int references organization(id),
    organization text not null
);

insert into organization(id, parent, organization)
values
    (1, null, '/'),
    (2, 1, '/a'),
    (3, 2, '/a/a'),
    (4, 1, '/b'),
    (5, 4, '/b/a'),
    (6, 4, '/b/b');

alter sequence organization_id_seq restart with 7;

create table user_info (
    id serial primary key,
    email text unique,
    organization int not null references organization(id)
);

insert into user_info (id, email, organization)
values
    (1, 'someone@a-a', 3),
    (2, 'someone@b-b', 6),
    (3, 'someone@a', 2),
    (4, 'someone@top', 1);

alter sequence user_info_id_seq restart with 5;

create function organization_descendant_users(organization_row organization)
returns setof user_info as $$
with recursive organization_descendant as (
    select
        id,
        id as descendant
    from organization
    union all
    select
        o.parent as id,
        n.descendant
    from organization o
    join organization_descendant n on n.id = o.id
)
select user_info.*
from organization_descendant
join user_info on user_info.organization = organization_descendant.descendant
where organization_descendant.id is not null and organization_descendant.id = organization_row.id
order by email;
$$ language sql stable;

create index on organization(parent);
create index on user_info(organization);

Track the user_info and organization tables and modify table organization and add the organization_descendant_users as a computed field on the table.

Example query:

query {
  organization {
    organization
    organization_descendant_users {
      email
    }
  }
}

Results:

{
  "data": {
    "organization": [
      {
        "organization": "/",
        "organization_descendant_users": [
          {
            "email": "someone@a"
          },
          {
            "email": "someone@a-a"
          },
          {
            "email": "someone@b-b"
          },
          {
            "email": "someone@top"
          }
        ]
      },
      {
        "organization": "/a",
        "organization_descendant_users": [
          {
            "email": "someone@a"
          },
          {
            "email": "someone@a-a"
          }
        ]
      },
      {
        "organization": "/a/a",
        "organization_descendant_users": [
          {
            "email": "someone@a-a"
          }
        ]
      },
      {
        "organization": "/b",
        "organization_descendant_users": [
          {
            "email": "someone@b-b"
          }
        ]
      },
      {
        "organization": "/b/a",
        "organization_descendant_users": []
      },
      {
        "organization": "/b/b",
        "organization_descendant_users": [
          {
            "email": "someone@b-b"
          }
        ]
      }
    ]
  }
}

Answer 2

No it's not possible to do because making recursive query request is not supported in GraphQL.

https://github.com/graphql/graphql-spec/issues/91

The best you can do is something along with:

org_unit {
  id
  name
  users {
    id
    display_name
  }
  child_org_unit {
    id
    name
    users {
      id
      display_name
    }
    child_org_unit {
      id
      name
      users {
        id
        display_name
      }
      child_org_unit {
        id
        name
        users {
          id
          display_name
        }
      }
    }
  }
}