[Known Issue] Mutations not correctly created for many-to-many relationship

[yorek]

Not sure if this is a bug or by design, but it seems that mutations do not reflect that many-to-many relationships. For example in a Book-Authors, when I use createBook to create a book I cannot specify the list of authors Id that should be associated with the book.

[Aniruddh25]

This is currently by design. We need to add support for such capability to modify nested entities.

[Aniruddh25]

Comes under nested mutations.

[severussundar]

Consider the tables: books, authors and book_author_link. There exists a many-many relationship between books and authors. The book_author_link is the bridge table.

Here are some of the things that needs consideration

1. Schema

   New mutations and new input types will be needed
   Proposal on how mutation could look like:

   createAuthor (item: {
           name: "Author Name",
           birthdate: "Author bdate",
           books: [
                {
                     title: "Book Title 1",
                     publisher_id: 1234
                },
                {
                     title: "Book Title 2",
                     publisher_id: 1234
                 }
                ]
            })

2. All the inserts should succeed to declare the mutation operation successful. Even if one of the inserts fail for some reason, the other inserts should be rolled back and the mutation operation should be declared unsuccessful.

3. The order of insertion in the tables matters.

   In this example, it is necessary that the insertions into authors and books tables occur first. Only after this insertion occurs, we are guaranteed to have ids which could be subsequently inserted in the linking table book_author_link

4. Since, there are multiple insertions happening, enclosing them within a transaction will make it easier to commit or rollback.

5. Validation to ensure that inputs with recursive items are prevented. Refer here

Please kindly let me know your thoughts

[Aniruddh25]

In this proposal, what will be the mutation schema? Do the books mentioned already exist? If not, is there a way I can provide already existing book to associate the new author with existing book? Can you please also come up with a design doc .md file similar to what I have for Nested Filtering - #957

[aaronpowell]

I had wondered this for a while, how could you create entity relationships. If I have a book and an author, how do I create the relationship between them?

[severussundar]

The relationships can be created using CLI by using the following commands

• Many - Many relationships

dab update Author --relationship books --target.entity Book --cardinality many --linking.object book_author_link

• One-One/One-Many relationships (Cardinality should be specified as one/many respectively)

dab update Publisher --relationship books --target.entity Book --cardinality many

Relationship fields:

• With a linking table (many-many relationships):

  "Author": {
    "source": "authors",
    "rest": true,
    "graphql": true,
    "permissions": [
      {
        "role": "anonymous",
        "actions": [ "read" ]
      },
      {
        "role": "authenticated",
        "actions": [ "create", "read", "update", "delete" ]
      }
    ],
    "relationships": {
      "books": {
        "cardinality": "many",
        "target.entity": "Book",
        "linking.object": "book_author_link"
      }
    }
  }

• Without a linking table (one-one or one-many relationships):

"Publisher": {
      "source": "publishers",
      "rest": true,
      "graphql": true,
      "permissions": [
        {
          "role": "anonymous",
          "actions": [ "read" ]
        },
        {
          "role": "authenticated",
          "actions": [ "create", "read", "update", "delete" ]
        }
      ],
      "relationships": {
        "books": {
          "cardinality": "many",
          "target.entity": "Book"
        }
      }
    }

[severussundar]

Moving this to March2023 as this is not in scope for SWA-DAB Public Preview

[Falven]

Is this still a limitation? Is there some sort of comprehensive log of DB operation limitations?

Here's the schema changes needed to support this for the example data:

...
input CreateAuthorInput {
  ...
  book_ids: [Int!]
}

input UpdateAuthorInput {
  ...
  book_ids: [Int!]
}

input CreateBookInput {
  ...
  author_ids: [Int!]
}

input UpdateBookInput {
  ...
  author_ids: [Int!]
}
...

Need to generate these with the corresponding resolver logic.

[aaronpowell]

I've been thinking about what could be a good approach here and my thought is that we should automatically generate the mutations required for editing relationships based off the relationship information present in the dab-config.json.

Let's take these entities and their relationship from the sample SQL database:

    "Author": {
      "source": "dbo.authors",
      "permissions": [
        {
          "role": "anonymous",
          "actions": ["*"]
        }
      ],
      "relationships": {
        "books": {
          "cardinality": "many",
          "target.entity": "Book",
          "linking.object": "dbo.books_authors"
        }
      }
    },
    "Book": {
      "source": "dbo.books",
      "permissions": [
        {
          "role": "anonymous",
          "actions": ["*"]
        }
      ],
      "relationships": {
        "authors": {
          "cardinality": "many",
          "target.entity": "Author",
          "linking.object": "dbo.books_authors"
        }
      }
    }

Presently, we have to map the book_authors table as an entity in the config (or use a stored procedure), but we already know the singular/plural of each entity (Book and Author), as well as the database table to insert/delete from (dbo.books_authors via linking.object), so why don't we automatically generate an entity named BookAuthor and then we can do the GraphQL mutations from that, basically we'd generate this:

    "BookAuthor": {
      "source": "dbo.books_authors",
      "permissions": [
        {
          "role": "anonymous",
          "actions": ["*"]
        }
      ]
    }

For you, which in turn would generate createBookAuthor, updateBookAuthor and deleteBookAuthor.

If we wanted to take it a step further, we could generate a more specific "relationship" entity in the internal object structure and only generate the create and delete mutation (since update doesn't make that much sense). Or rather than creating singular create/delete, we could have it generate bulk ones, such as:

mutation Mutations {
  createBookAuthors(bookId: Int! authorIds: [Int!]!): Book!
  deleteBookAuthors(bookId: Int! authorIds: [Int!]!): Book!
  createAuthorBooks(authorId: Int! bookIds: [Int!]!): Author!
  deleteAuthorBooks(authorId: Int! bookIds: [Int!]!): Author!
}

These would be specific mutations representing the relationship direction (starting with Book or Author) and allowing a list of IDs to add as the relationship.

This can become more complex when the mapping table expands beyond two columns, but I'm not sure how common that is.

[seantleonard]

@ayush3797 @severussundar can this item be tracked with nested mutations, if required?

[severussundar]

Hey @seantleonard, through the nested inserts feature, they would be able to accomplish what is described in the description ---> Create a book + associate it with a list of authors; both in a single operation.

I've linked this issue to the parent issue for nested mutations. This is added as a related item in the parent issue.

[severussundar]

Through multiple create feature, this use-case is enabled now. When creating Books, Authors of those books can also be created.

mutation {
  createbook(
    item: {
      title: "Book #1"
      publisher_id: 1234
      authors: [
        { name: "Author #1", birthdate: "2001-01-01" }
        { name: "Author #2", birthdate: "2000-01-02" }
      ]
    }
  ) {
    id
    title
    authors {
      items {
        id
        name
        birthdate
      }
    }
  }
}

Note: Existing authors cannot be linked when creating a book through multiple create operation. In other words, there's no option to specify existing ids in the input object type for M:N relationships.