Join Table (explicit many-to-many relationship) customization

[tjustsoup]

Our primary goal is to be able to add/define properties of a “join table” in a many-to-many relationship. We aren't quite there yet, but I wanted to share what we've accomplished so far. We started with the standard Keystone many-to-many relationships, using two lists in our schema and relating them to each other.

Our first solution was to treat the join-table as just another list. If we have “Post” and “Tag” as lists, we will also have “Post_Tag” as a list. Post has an m-to-1 relationship with Post_Tag, as does Tag with Post_Tag. Technically this works, but it has a few issues both in terms of UI/UX and backend (tables).

1. When I use the interface to create/edit a Post, I now have the ability to assign “Post_Tags” instead of “Tags”. And while technically this is what we intend to do, I don’t get to see which “Tags” exist or create and apply new “Tags” like I could in the standard m-to-n relationship. Instead, that now applies to the “Post_Tags”. I as the user now have to manually create this “Post_Tag”, which is bad.
2. In the join-table, you can create two separate “Post_Tags” that contain the same “Post” id and “Tag” id - i.e. this doesn’t enforce uniqueness. The reason this can happen is because those are technically two different “Post_Tags” in the table, distinguished by their own “Post_Tag” ids. The id column doesn’t need to exist because each pairing of “Post” id and “Tag” id should be guaranteed to be unique, and therefore the identifier for each row can just be a combination of both ids.

Our next solution was to pass something into the schema that would be handled by the core code - specifically the “printPrismaSchema” and “initialiseLists” functions - in the hopes of handling this dynamically. Doing some research into Prisma led us to “implicit” vs “explicit” m-to-n relationship models. Say I want the “Post” interface to allow me to add a “Note” (user input string) to each “Tag” that I’m creating a relation to. We would be editing the config passed into the “relationship” field function of the list “Post” in the schema. Specifically, we added the optional property db.explicitFields with type BaseFields<ListTypeInfo>. This would look exactly like the “fields” property of the “list” config. In our hypothetical, our “Post” list would look like this:

 Post: list({
    access: allowAll,
    fields: {
      title: text(),
      content: text(),
      tags: relationship({
        ref: "Tag.posts",
        many: true,
        db: {
          explicitFields: {
            notes: text()
          }
        }
      }),
    },
  }),

We knew that we needed to make printPrismaSchema() able to print an explicit m-to-n relationship. However, this received its parameters from theinitialiseLists(), which handles lots of things including initialising fields and resolving relationships. getListsWithInitialisedFields() turns those field functions from the schema into data that’s used in resolveRelationships(), which itself establishes relationships with terms (strings) we need in printPrismaSchema().

We tried starting in printPrismaSchema(), handling the case of when to “print” an explicit m-to-n relationship (not yet concerned about handling our “explicitFields”). Unfortunately, even though the Prisma schema models were correct, the Keystone interface ran into a “Prisma Error” when trying to do any CRUD operations regarding the relationship between “Posts” and “Tags”. We knew we had to start handling this inside of initialiseLists(), either AFTER resolveRelationships() or by somehow changing the whole process altogether.
Instead, we reconsidered our first option, treating the join-table as a defined list in our Keystone schema. This time, however, we used the extendPrismaSchema the “Post_Tag” list to change the Prisma schema:

  Post_tag: list({
    access: allowAll,
    fields: {
      notes: text(),
      post: relationship({
        ref: "Post.tags",
        many: false,
        db: {
          extendPrismaSchema(field) {
            return field.replaceAll(/\?/g, "");
          },
        },
      }),
      tag: relationship({
        ref: "Tag.posts",
        many: false,
        db: {
          extendPrismaSchema(field) {
            return field.replaceAll(/\?/g, "");
          },
        },
      }),
    },
    db: {
      extendPrismaSchema(schema) {
        let result = schema.replace(/\}/g, "");
        result += `
          @@unique([postId, tagId])
        }
        `;
        return result;
      },
    },
  }),

Its relationship fields of “post” and “tag” are both using extendPrismaSchema to make those fields required, and the list itself is using extendPrismaSchema to enforce a unique combination of “postId” and “tagId”. In that same block, we also tried adding @@id([postId, tagId], map: “id”) to prevent the table from needing an ID column, but that threw errors when trying to establish relationships in the interface. While we still have the “id” column, this does fix the issue of duplicate “Post_Tags”.

To fix the UI/UX issue, the first step is to change the config for “tags” in “Post”:

      tags: relationship({
        ref: "Post_tag.post",
        many: true,
        ui: {
          displayMode: "cards",
          cardFields: ["tag"],
          inlineCreate: { fields: ["tag", "notes"] },
          inlineEdit: { fields: ["tag", "notes"] },
        },
      }),

This isn’t perfect UX but it’s definitely usable! That said, we still need to make sure the correct CRUD operations are happening. For that, I think the next step will be using hooks here, and possibly a virtual field?

[lmiller1990]

Did you have much luck with this? I, too, am trying to a many-to-many relationship but am not having much luck specifying the join table manually.