Remove user while also removing unused tags in many-to-many relationship in GraphQL?

I'm using Hasura for this.

Say for example I have a users, tags and users_tags table in a many-to-many relationship.

User's can define multiple tags (like a persons hobbies) and each tag can belong to multiple people.

Table users {
  id int [pk increment]
}

Table tags {
  id int [pk increment]
  name text
}

Table users_tags {
  id int [pk increment]
  user [ref: - user.id]
  tag [ref: - tags.id]
}

The users_tags table is a many-to-many table.

When a new user is created, I can create new tags and associate them with users just fine.

But when a user is deleted, what GraphQL mutation can I use to remove tags from both tags and users_tags if they don't point to other users.

Solution

This depends on your desired data model. If a tag is always associated with one user only, you can get rid of the users_tags table and add the user to the tags table.

Then, if you've set up a foreign key on the tag table that references the id field on the user table then this can be done automatically.

You will be given 4 options on deletion:

restrict: Does not allow you to delete a user that is referenced on a tag
cascade: Deletes the tag if the user is deleted
set null: Sets this row as null if the user is deleted
set default: Sets this row as the default value if the user is deleted

You'll want to select the cascade option here.

If you want to keep your current data model you will be able to do this cascading on the users_tags table if the tag or user is deleted but will need some process in place to delete tags that have no users associated to them.

The easiest way would probably be to add a custom SQL function but you could also have the following graphql query to find the tags that have 0 users_tags associated with them and then provide a list of primary keys to a delete tags mutation.

query CountOfUsersTagsForTag {
  tags {
    id
    users_tags_aggregate {
      aggregate {
        count
      }
    }
  }
}