Build Sports Team API with GraphQL - Hasura - Part 2

This post is part of a series of post and so if you haven't read the previous one, please read that and come back and we will wait :)

Objectives of this post

Introduction
What is one-to-one table relationship
Creating support staff table
Linking the Teams table with support staff
Update the relationship in Hasura
Fetching the data

Introduction

In the first part, we learned how to create a team table and how to fetch data from the table using the GraphQL queries.

BUILD SPORTS TEAM API WITH GRAPHQL - HASURA - PART 1

Before we start with adding players to the team we will first focus on adding a head coach for the team. We will learn about one-to-one table relationship.

What is one-to-one table relationship

For our team, there can be only one Head Coach and for a Head Coach position there is only one team which he manages. This relationship is called the one-to-one relationship and it can be expressed by adding a foreign key to the table. Foreign key refers to primary key of other tables.

Creating support staff table

Let's create a support staff table which will have the details of the support staff. We can have simple information about the staff and you can expand on the data if you need.

Linking the Teams table with support staff

Open the team table which we have created in the part 1 and add a new column as below

Column name: head_coach_id, Type: Text, Nullable: True

Once we have the new column, we need to add a Foreign key relationship to the SupportStaff table so that teams table knows that this column references the row in the other table.

Open the Modify tab and then move to the Foreign Keys section. You can add a new foreign key linking the other table id column in this place

Update the relationship in Hasura

Once you have the foreign key relationship defined, we still have to create a relationship in Hasura GraphQL engine to make it easier to access the data inside the SupportStaff table

WHY ?

Right now once we insert the data into the SupportStaff table and then add that id in the team table and make a simple GraphQL query to get the data, It would be just the actual id of the head_coach which will be returned

query MyQuery {
  teams {
    team_name
    head_coach_id
  }
}

OUTPUT:
{
  "data": {
    "teams": [
      {
        "team_name": "Australia Cricket Team",
        "head_coach_id": 2
      },
      {
        "team_name": "Indian Cricket Team",
        "head_coach_id": 1
      },
      {
        "team_name": "England Cricket Team",
        "head_coach_id": 3
      }
    ]
  }
}

This is not so useful to the end user. When displaying the data, We might want to show the name of the coach instead of the id. So we can create a relationship in Hasura to make this simple when fetching data.

Open the Relationships tab in teams table and then you can see that Hasura will suggest a Object Relationships. Add the relationship and you can name it head_coach

Fetching the data

Once this is done you can now query for the head_coach name using the below GraphQL Query. Now the name which we gave in the relationship will be acting as a separate field and you can drill down into that field to find the data which is present in the other table. So in a single query you have the data from two separate tables and you can present this data easily in the UI of your app.

query MyQuery {
  teams {
    team_name
    head_coach_id
    head_coach {
      staff_name
    }
  }
}

OUTPUT:
{
  "data": {
    "teams": [
      {
        "team_name": "Australia Cricket Team",
        "head_coach_id": 2,
        "head_coach": {
          "staff_name": "Staff 2"
        }
      },
      {
        "team_name": "Indian Cricket Team",
        "head_coach_id": 1,
        "head_coach": {
          "staff_name": "Staff 1"
        }
      },
      {
        "team_name": "England Cricket Team",
        "head_coach_id": 3,
        "head_coach": {
          "staff_name": "Staff 3"
        }
      }
    ]
  }
}