Modelling one-to-many table relationships¶

Introduction¶

A one-to-many relationship between two tables can be established via a foreign key constraint.

Say we have the following two tables in our database schema:

author (
  id SERIAL PRIMARY KEY,
  name TEXT
)

article (
  id SERIAL PRIMARY KEY,
  author_id INT
  title TEXT
  ...
)

These two tables are related via a one-to-many relationship. i.e:

• an author can have many articles
• an article has one author

Step 1: Set up a table relationship in the database¶

This one-to-many relationship can be established in the database by:

1. Adding a foreign key constraint from the article table to the author table using the author_id and id columns of the tables respectively.

This will ensure that the value of author_id column in the article table is present in the id column of the author table.

Step 2: Set up GraphQL relationships¶

To access the nested objects via the GraphQL API, create the following relationships:

• Array relationship, articles from author table using article :: author_id  ->  id
• Object relationship, author from article table using author_id -> author :: id

Step 3: Query using relationships¶

We can now:

• fetch a list of authors with their articles:

  query {
    author {
      id
      name
      articles {
        id
        title
      }
    }
  }
  

  query { author { id name articles { id title } } }

  { "data": { "author": [ { "id": 1, "name": "Justin", "articles": [ { "id": 15, "title": "vel dapibus at" }, { "id": 16, "title": "sem duis aliquam" } ] }, { "id": 2, "name": "Beltran", "articles": [ { "id": 2, "title": "a nibh" }, { "id": 9, "title": "sit amet" } ] } ] } }

• fetch a list of articles with their author:

  query {
    article {
      id
      title
      author {
        id
        name
      }
    }
  }
  

  query { article { id title author { id name } } }

  { "data": { "article": [ { "id": 1, "title": "sit amet", "author": { "id": 4, "name": "Anjela" } }, { "id": 2, "title": "a nibh", "author": { "id": 2, "name": "Beltran" } } ] } }