Modelling one-to-many table relationships¶
Table of contents
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
author
can have manyarticles
- an
article
has 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
articles
table to theauthors
table using theauthor_id
andid
columns 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,
articles
fromauthors
table usingarticles :: author_id -> id
- Object relationship,
author
fromarticles
table usingauthor_id -> authors :: id
Query using one-to-many relationships¶
We can now:
fetch a list of
authors
with theirarticles
:query { authors { id name articles { id title } } }
query { authors { id name articles { id title } } }{ "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" } ] } ] } }fetch a list of
articles
with theirauthor
:query { articles { id title author { id name } } }
query { articles { id title author { id name } } }{ "data": { "articles": [ { "id": 1, "title": "sit amet", "author": { "id": 4, "name": "Anjela" } }, { "id": 2, "title": "a nibh", "author": { "id": 2, "name": "Beltran" } } ] } }
Insert using one-to-many relationships¶
We can now:
- insert an
author
with theirarticles
where the author might already exist (assume uniquename
forauthor
):
mutation 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
}
}
}
}
mutation 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
}
}
}
}
{
"data": {
"insert_author": {
"returning": [
{
"name": "Felix",
"articles": [
{
"title": "Article 1",
"content": "Article 1 content"
},
{
"title": "Article 2",
"content": "Article 2 content"
}
]
}
]
}
}
}
- insert
articles
with theirauthor
where theauthor
might already exist (assume uniquename
forauthor
):
mutation 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
}
}
}
}
mutation 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
}
}
}
}
{
"data": {
"insert_article": {
"returning": [
{
"title": "Article 1",
"content": "Article 1 content",
"author": {
"name": "Alice"
}
},
{
"title": "Article 2",
"content": "Article 2 content",
"author": {
"name": "Alice"
}
}
]
}
}
}
Note
You can avoid the on_conflict
clause if you will never have conflicts.