Build Sports Team API with GraphQL - Hasura - Part 4

Learn about many-to-many database relationship by building sports team API

Build Sports Team API with GraphQL - Hasura - Part 4

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 many-to-many relationship
Creating the players table
How to create many-to-many relationship
Creating the table in Hasura for player recruitment
Adding foreign key to the player recruitment table
Adding array relationships in tables
Adding data to the recruitment table
Query the data

Introduction

In the previous post, we learned how to create one-to-many relationship with the teams table.

BUILD SPORT TEAM API WITH GRAPHQL - HASURA - PART 3

In this post, we will learn about creating a many-to-many relationship and how to add much awaited players to the team.

What is many-to-many relationship

Consider the example of players playing for a particular sport, they will be part of many team whether it is their national team, club team, league team and so on. Also when you consider a team, there will be many player who are part of the team. So it many to many relationship between teams and players.

Creating the players table

Column name: id, Type: Integer (auto-increment)

Column name: created_at, Type: Timestamp

Column name: updated_at, Type: Timestamp

Column name: name, Type: Text

Column name: age, Type: Integer

Column name: jersey_number, Type: Integer, Nullable: true (This field can be left empty)

Primary Key: id

How to create many-to-many relationship

When we are doing one-to-one relationship or one-to-many relationships, we can just add a new column and set it as a foreign key and create a new relationship in Hasura. Since we want to have list of key of the players table in teams table, we cannot add a new column

We need to create a new table which represents the relationship between players and teams. In doing so, each row in that new table will represent a player belonging to one team.

We can make use of this new table to add more info to the association between players and team by setting up a date of joining the team, and salary details of the players for that team and so on.

How to create many-to-many relationship

Creating the table in Hasura for player recruitment

Create a new table with the following fields based on the image below. You can add any relavant data you would need when you are forming this many-to-many relationship

Creating the table in Hasura for player recruitment

Adding foreign key to the player recruitment table

After creating the table, we need to specify that the two column player_id and team_id are foreign key pointing to their respective tables

Adding foreign key to the player recruitment table 1

Adding foreign key to the player recruitment table 2

Adding array relationships in tables

Now, we just need to add the array relationship on both the tables which will let us access the list of players from the teams query and access the list of teams from the players query.

Open the Relationship tab in the players table and then add the suggested array relationship and name the relationship as teams

Adding array relationships in tables 1

Open the Relationship tab in the teams table and then add the suggested array relationship and name the relationship as players

Adding array relationships in tables 2

After we add the array relationship, we will be able to access the id of the player from the teams query when we drill down. This might not be very useful as we need the player info to display in the UI. So we need to add object relationship in the recruitment table like one-to-one relationship which will let us further drill down to the name of the player or team which they are part of.

Finally add the two suggested object relationship in the player_team_recruitments table which we created before

Adding array relationships in tables 3

Adding data to the recruitment table

We have all the relationships definded, so next step is populate the data in the table. We can just take the primary key of the player and primary key of the team which they belong and then create row with that details

Adding data to the recruitment table

Query the data

We will all the hard work till now to setup the api which has proper relationships between the players and teams. Now we can reap the benefits by using the query to fetch those data in a single query

Lets look at the querying the list of team with all the players

query MyQuery {
  teams {
    team_name
    players {
      player {
        name
        age
      }
    }
  }
}


OUTPUT:
{
  "data": {
    "teams": [
      {
        "team_name": "Australia Cricket Team",
        "players": [
          {
            "player": {
              "name": "Steve Smith",
              "age": 31
            }
          }
        ]
      },
      {
        "team_name": "Indian Cricket Team",
        "players": [
          {
            "player": {
              "name": "Virat Kolhi",
              "age": 30
            }
          },
          {
            "player": {
              "name": "Rohit Sharma",
              "age": 28
            }
          },
          {
            "player": {
              "name": "MS Dhoni",
              "age": 34
            }
          },
          {
            "player": {
              "name": "Bhuvaneshvar Kumar",
              "age": 29
            }
          },
          {
            "player": {
              "name": "Jasprit Bumrah",
              "age": 26
            }
          }
        ]
      },
      {
        "team_name": "England Cricket Team",
        "players": [
          {
            "player": {
              "name": "Joe Root",
              "age": 30
            }
          }
        ]
      }
    ]
  }
}

Lets look at the querying the list of player and their teams

query MyQuery {
  players {
    name
    teams {
      team {
        team_name
      }
    }
  }
}


OUTPUT:
{
  "data": {
    "players": [
      {
        "name": "Virat Kolhi",
        "teams": [
          {
            "team": {
              "team_name": "Indian Cricket Team"
            }
          }
        ]
      },
      {
        "name": "Rohit Sharma",
        "teams": [
          {
            "team": {
              "team_name": "Indian Cricket Team"
            }
          }
        ]
      },
      {
        "name": "MS Dhoni",
        "teams": [
          {
            "team": {
              "team_name": "Indian Cricket Team"
            }
          }
        ]
      },
      {
        "name": "Bhuvaneshvar Kumar",
        "teams": [
          {
            "team": {
              "team_name": "Indian Cricket Team"
            }
          }
        ]
      },
      {
        "name": "Jasprit Bumrah",
        "teams": [
          {
            "team": {
              "team_name": "Indian Cricket Team"
            }
          }
        ]
      },
      {
        "name": "Steve Smith",
        "teams": [
          {
            "team": {
              "team_name": "Australia Cricket Team"
            }
          }
        ]
      },
      {
        "name": "Joe Root",
        "teams": [
          {
            "team": {
              "team_name": "England Cricket Team"
            }
          }
        ]
      }
    ]
  }
}