Modelling many-to-many table relationships¶

Introduction¶

A many-to-many relationship between two tables can be established by creating a table typically called as bridge/junction/join table and adding foreign-key constraints from it to the original tables.

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

articles (
  id SERIAL PRIMARY KEY,
  title TEXT
  ...
)

tags (
  id SERIAL PRIMARY KEY,
  tag_value TEXT
  ...
)

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

• an article can have many tags
• a tag has many articles

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

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

1. Creating a bridge table called article_tag with the following structure:

   article_tag (
     article_id INT
     tag_id INT
     PRIMARY KEY (article_id, tag_id)
     ...
   )
   

2. Adding foreign key constraints from the article_tag table to:

   • the articles table using the article_id and id columns of the tables respectively
   • the tags table using the tag_id and id columns of the tables respectively

The table article_tag sits between the two tables involved in the many-to-many relationship and captures possible permutations of their association via the foreign keys.

Step 2: Set up GraphQL relationships¶

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

• Array relationship, article_tags from articles table using article_tag :: article_id -> id
• Object relationship, tag from article_tag table using tag_id -> tags :: id
• Array relationship, tag_articles from tags table using article_tag :: tag_id -> id
• Object relationship, article from article_tag table using article_id -> articles :: id

Query using many-to-many relationships¶

We can now:

• fetch a list of articles with their tags:

  query {
    articles {
      id
      title
      article_tags {
        tag {
          id
          tag_value
        }
      }
    }
  }
  

  query { articles { id title article_tags { tag { id tag_value } } } }

  { "data": { "articles": [ { "id": 1, "title": "sit amet", "article_tags": [ { "tag": { "id": 1, "tag_value": "mystery" } }, { "tag": { "id": 2, "tag_value": "biography" } } ] }, { "id": 2, "title": "a nibh", "article_tags": [ { "tag": { "id": 2, "tag_value": "biography" } }, { "tag": { "id": 5, "tag_value": "technology" } } ] } ] } }

• fetch a list of tags with their articles:

  query {
    tags {
      id
      tag_value
      tag_articles {
        article {
          id
          title
        }
      }
    }
  }
  

  query { tags { id tag_value tag_articles { article { id title } } } }

  { "data": { "tags": [ { "id": 1, "tag_value": "mystery", "tag_articles": [ { "article": { "id": 1, "title": "sit amet" } } ] }, { "id": 2, "tag_value": "biography", "tag_articles": [ { "article": { "id": 1, "title": "sit amet" } }, { "article": { "id": 2, "title": "a nibh" } } ] } ] } }

Insert using many-to-many relationships¶

We can now:

• insert an article with tags where the tag might already exist (assume unique value for tag):

mutation insertArticleWithTags {
  insert_article(objects: [
    {
      title: "Article 1",
      content: "Article 1 content",
      author_id: 1,
      article_tags: {
        data: [
          {
            tag: {
              data: {
                value: "Recipes"
              },
              on_conflict: {
                constraint: tag_value_key,
                update_columns: [value]
              }
            }
          }
          {
            tag: {
              data: {
                value: "Cooking"
              },
              on_conflict: {
                constraint: tag_value_key,
                update_columns: [value]
              }
            }
          }
        ]
      }
    }
  ]) {
    returning {
      title
      article_tags {
        tag {
          value
        }
      }
    }
  }
}

• insert a tag with articles where the tag might already exist (assume unique value for tag):

mutation insertTagWithArticles {
  insert_tag(objects: [
    {
      value: "Recipes",
      article_tags: {
        data: [
          {
            article: {
              data: {
                title: "Article 1",
                content: "Article 1 content",
                author_id: 1
              }
            }
          },
          {
            article: {
              data: {
                title: "Article 2",
                content: "Article 2 content",
                author_id: 1
              }
            }
          }
        ]
      }
    }
  ],
  on_conflict: {
    constraint: tag_value_key,
    update_columns: [value]
  }
  ) {
    returning {
      value
      article_tags {
        article {
          title
        }
      }
    }
  }
}

Fetching relationship information¶

The intermediate fields article_tags & tag_articles can be used to fetch extra information about the relationship. For example, you can have a column like tagged_at in the article_tag table which you can fetch as follows:

query {
  articles {
    id
    title
    article_tags {
      tagged_at
      tag {
        id
        tag_value
      }
    }
  }
}

Flattening a many-to-many relationship query¶

In case you would like to flatten the above queries and avoid the intermediate fields article_tags & tag_articles, you can create the following views additionally and then query using relationships created on these views:

CREATE VIEW article_tags_view AS
  SELECT article_id, tags.*
    FROM article_tag LEFT JOIN tags
      ON article_tag.tag_id = tags.id

CREATE VIEW tag_articles_view AS
  SELECT tag_id, articles.*
    FROM article_tag LEFT JOIN articles
      ON article_tag.article_id = articles.id

Now create the following relationships:

• Array relationship, tags from the articles table using article_tags_view :: article_id -> id
• Array relationship, articles from the tags table using tag_articles_view :: tag_id -> id

We can now:

• fetch articles with their tags without an intermediate field:

  query {
    articles {
      id
      title
      tags {
        id
        tag_value
      }
    }
  }
  

  query { articles { id title tags { id tag_value } } }

  { "data": { "articles": [ { "id": 1, "title": "sit amet", "tags": [ { "id": 1, "tag_value": "mystery" }, { "id": 3, "tag_value": "romance" } ] }, { "id": 2, "title": "a nibh", "tags": [ { "id": 5, "tag_value": "biography" }, { "id": 3, "tag_value": "romance" } ] } ] } }

  • fetch tags with their articles without an intermediate field:

    query {
      tags {
        id
        tag_value
        articles {
          id
          title
        }
      }
    }
    

    query { tags { id tag_value articles { id title } } }

    { "data": { "tags": [ { "id": 1, "tag_value": "mystery", "articles": [ { "id": 1, "title": "sit amet" } ] }, { "id": 2, "tag_value": "biography", "articles": [ { "id": 1, "title": "sit amet" }, { "id": 2, "title": "a nibh" } ] } ] } }