Postgres with Drizzle · Chapter 15 of 40

Many to many

By the end of this chapter you can link rows through a table in the middle, read across it in either direction, remove one link, and count the links of every row, zero included. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: JSON & Relations
  • 5 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.

Why you need this

Meera wants tags on posts: a post about Drizzle can be tagged drizzle and typescript, and the drizzle tag page lists every post that has it. Rohan wants to see how many posts use each tag, including tags nobody uses yet, so he can tidy them up.

Chapter 14's foreign key works when each post has one author. A post can have many tags and a tag many posts, so neither table can hold the other one's id. A text column with "drizzle,typescript" in it cannot be joined, counted or kept in step when a tag is renamed. The answer is a third table that holds one row per link.

What you build: 5 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    addTagToPost: link a tag to a post

    Write addTagToPost(postId, tagId). Both are numbers. It adds one row to the post_tags junction table linking that post to that tag.

    Return the inserted link as one object, through Drizzle: the check reads postId and tagId.

  2. Task 2

    getPostTags: the tags on one post

    Write getPostTags(postId). postId is a number. It returns the tags linked to that post, by joining the post_tags junction table to the tags table.

    Return an array of objects with exactly two keys, tagName (the tag's name) and tagId (the tag's id). Any order: the check sorts the names. Task 4's check also calls this function.

  3. Task 3

    getPostsByTag: the posts with one tag, by name

    Write getPostsByTag(tagName). tagName is a string such as graphql. It returns the posts that carry the tag with that name, going from posts through post_tags to tags.

    Return an array of objects with postId (the post's id) and content. Any order. With the check's data, two of the three posts carry graphql.

  4. Task 4

    removeTagFromPost: unlink one tag

    Write removeTagFromPost(postId, tagId). Both are numbers. It deletes the one post_tags row linking that post to that tag; the post and the tag themselves stay.

    Return the deleted link as one object, through Drizzle: the check reads postId and tagId, then calls your getPostTags (task 2) to see that the post has no tags left, so task 2 must work too.

  5. Task 5

    getTagsWithPostCount: posts per tag, zero included

    Write getTagsWithPostCount(). It returns one row per tag, including tags on no post, with how many posts carry it.

    Return an array of objects with name (the tag's name) and postCount, a number (0 for an unused tag). Any order: the check finds each tag by name. With the check's data, popular has 2, niche 1 and unused 0.

What you will learn

  • A junction table: post_tags holds one row per post and tag pair, and nothing else.
  • Joining through it: one join to reach the other side, two joins to filter by a column on the far side.
  • Removing one link: a delete that names both ends with and.
  • Counting links with leftJoin: zero for rows with no links.

More in JSON & Relations

Build "Many to many" yourself

MingleSphereQL grows up: its backend talks to Postgres through Drizzle. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.