Modeling 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:
authors (
id SERIAL PRIMARY KEY,
name TEXT
)
articles (
id SERIAL PRIMARY KEY,
author_id INT
title TEXT
...
)
These two tables are related via a one-to-many relationship. i.e:
- an
authorcan have manyarticles - an
articlehas oneauthor
Step 1: Set up a table relationship in the database
This one-to-many relationship can be established in the database by:
- Adding a foreign key constraint from the
articlestable to theauthorstable using theauthor_idandidcolumns of the tables respectively.
This will ensure that the value of author_id column in the articles table is present in the id column of the
authors table.
Step 2: Set up GraphQL relationships
To access the nested objects via the GraphQL API, create the following relationships:
- Array relationship,
articlesfromauthorstable usingarticles :: author_id -> id - Object relationship,
authorfromarticlestable usingauthor_id -> authors :: id
Query using one-to-many relationships
We can now:
- fetch a list of
authorswith theirarticles:
GraphiQL
xxxxxxxxxxquery { authors { id name articles { id title } } }Query Variables
Request Headers
x
{ "data": { "authors": [ { "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" } ] } ] } }No Schema Available
- fetch a list of
articleswith theirauthor:
GraphiQL
xxxxxxxxxxquery { articles { id title author { id name } } }Query Variables
Request Headers
1
xxxxxxxxxx{ "data": { "articles": [ { "id": 1, "title": "sit amet", "author": { "id": 4, "name": "Anjela" } }, { "id": 2, "title": "a nibh", "author": { "id": 2, "name": "Beltran" } } ] } }No Schema Available
Insert using one-to-many relationships
We can now:
- insert an
authorwith theirarticleswhere the author might already exist (assume uniquenameforauthor):
GraphiQL
xxxxxxxxxxmutation UpsertAuthorWithArticles { insert_author(objects: { name: "Felix", articles: { data: [ { title: "Article 1", content: "Article 1 content" }, { title: "Article 2", content: "Article 2 content" } ] } }, on_conflict: { constraint: author_name_key, update_columns: [name] } ) { returning { name articles { title content } } } }Query Variables
Request Headers
1
xxxxxxxxxx{ "data": { "insert_author": { "returning": [ { "name": "Felix", "articles": [ { "title": "Article 1", "content": "Article 1 content" }, { "title": "Article 2", "content": "Article 2 content" } ] } ] } } }No Schema Available
- insert
articleswith theirauthorwhere theauthormight already exist (assume uniquenameforauthor):
GraphiQL
xxxxxxxxxxmutation upsertArticleWithAuthors { insert_article(objects: [ { title: "Article 1", content: "Article 1 content", author: { data: { name: "Alice" }, on_conflict: { constraint: author_name_key, update_columns: [name] } } }, { title: "Article 2", content: "Article 2 content", author: { data: { name: "Alice" }, on_conflict: { constraint: author_name_key, update_columns: [name] } } } ]) { returning { title content author { name } } } }Query Variables
Request Headers
1
xxxxxxxxxx{ "data": { "insert_article": { "returning": [ { "title": "Article 1", "content": "Article 1 content", "author": { "name": "Alice" } }, { "title": "Article 2", "content": "Article 2 content", "author": { "name": "Alice" } } ] } } }No Schema Available
Note
You can avoid the on_conflict clause if you will never have conflicts.
