Many-To-Many

[rygo6]

Was charging forth on this amplify graphql annotation stuff, until I noticed this from the documentation:

The @connection directive enables you to specify relationships between @model object types. Currently, this supports one-to-one, one-to-many, and many-to-one relationships. An error is thrown if you try to configure a many-to-many relationship.

Unfortunately my app is many-to-many. So that's a bummer. I am curious, when will many-to-many relationship work?

Also, could you please as advise on how to do this? I presume I can just forego the @connection annotation and then attach my own resolver which will do some kind of many-to-many look up. Will that work? Will I be able to mix the @searchable annotations with this?

[mikeparisstuff]

You can implement many to many yourself using two 1-M @connections and a joining @model. For example:

type Post @model {
  id: ID!
  title: String!
  editors: [PostEditor] @connection(name: "PostEditors")
}
# Create a join model and disable queries as you don't need them
# and can query through Post.editors and User.posts
type PostEditor @model(queries: null) {
  id: ID!
  post: Post! @connection(name: "PostEditors")
  editor: User! @connection(name: "UserEditors")
}
type User @model {
  id: ID!
  username: String!
  posts: [PostEditor] @connection(name: "UserEditors")
}

You can then create Posts & Users independently and join them in a many-to-many by creating PostEditor objects. In the future we will support many to many out of the box and will manage setting up the join model for you and configuring better names for the mutations that relate objects. In the future we will also support using batch operations to create bi-directional relationships like friendships that may have some qualifying information on the joining model itself.

[ev-dev]

Looking forward to more support for association features, having to explicitly define the association types in cases like the example (where no additional columns are needed) can grow a bit out of hand and lead to confusions for larger data schemas that have lots of many-to-many associations. Definitely loses some of the readability that's so nice with defining data models via GraphQL SDL.

Ideally the above could be written as:

type Post {
  id: ID!
  title: String!
  editors: [User!]!
}
type User {
  id: ID!
  username: String!
  posts: [Post!]!
}

The Prisma project has support for this and it's real nice to work with. Not sure the comparison is entirely fair tho as AppSync's project focus seems to be more along integrating a variety of data sources while Prisma has been far slower in adopting more sources but is arguably more feature rich at the model declaration level

[mikeparisstuff]

@ev-dev This is exactly the type of behavior that will be supported in the future. There is a TODO here https://github.com/aws-amplify/amplify-cli/blob/master/packages/graphql-connection-transformer/src/ModelConnectionTransformer.ts#L132 that is waiting to be worked on and will automatically handle hooking up the join table and additional mutations to enable the many to many.

[affablebloke]

@mikeparisstuff Are you able to elaborate on the details of how many-to-many connections will be modeled in DynamoDB. For example, will it create another GSI or use some form of GSI Overloading? I haven't messed around with amplify-cli in a while, but it seems like creating a GSI for every relationship will hit DynamoDB's limit of five global secondary indexes per table quite fast.

The reason I ask, is that we are evaluating the use of amplify-cli for an upcoming greenfield project. We have been using AWS Appsync and love it 😃 . Keep up the good work 👍

[mikeparisstuff]

@affablebloke I will write up a more detailed design and post it here soon. I have been looking into both a general adjacency list approach as well overloading GSIs to get around the GSI limit issues but this may need to be made configurable because there are pros/cons to the approaches. Do you have any approaches in mind that I have not mentioned?

Thanks for the kind words! I'm glad to hear you are enjoying the service thus far!

[austinamorusocfc]

@mikeparisstuff this service has been a god send please keep up the good work

[affablebloke]

@mikeparisstuff Sounds good 👍. Those are the same strategies that we use at our company.

[mikeparisstuff]

For future reference, we have new docs on @connection here https://aws-amplify.github.io/docs/js/graphql#connection.

[LoganArnett]

What is the best way to use this relationship in practice? I have my Many to Many relationships setup but it is unclear what the best way is to utilize the join table. Is it just an additional mutation of creating that join model with the previously created models that you want to relate?

[mwarger]

@mikeparisstuff It appears to me that a recent update to the CLI has broken this approach. I believe it's recent only because I had one of these setup at one point and it was working. I thought I didn't need it anymore so I removed it. Now I'm going to add it back again and I'm getting the error below.

Cloudformation gives me the following error when attempting to create a similar many-to-many relationship: Only one resolver is allowed per field. (Service: AWSAppSync; Status Code: 400; Error Code: BadRequestException; Request ID: 06480039-0ade-11e9-aca0-3707b99c7ce2

Edit: I re-created my project for another environment and didn't receive this error again.

[mwarger]

What is the best way to use this relationship in practice? I have my Many to Many relationships setup but it is unclear what the best way is to utilize the join table. Is it just an additional mutation of creating that join model with the previously created models that you want to relate?

Yes, that's the way it works. You create the model using the mutation with the IDs of the two related items.

[lobosan]

Any updates on this? because I'm about to start a new project that needs lots of many-to-many relationships, so I'm wondering what is the correct way to create the schema of if I should wait until this gets delivered

[shurmanidze]

@mikeparisstuff

You can then create Posts & Users independently and join them in a many-to-many by creating PostEditor objects.

Does it mean that if I query a User Posts via the PostEditor intermediate entity, then it will cause multiple reads on DynamoDB? For instance, 20 user posts will lead to 20 reads on the Posts table instead of one if I want to display a Post title for each PostEditor link in my UI. It may consume the DynamoDB throughput inefficiently. Is it recommended to denormalize data from User and Post into PostEditor manually to prevent the mentioned extra data requests? Is it going to be solved somehow in the future?

[tezqa]

I'm waiting too for this update. I heavily need many-to-many relationship for my new project and I would prefer to avoid adding models just to create this relationship...

[flybayer]

First-class many-to-many support is a definite need for me too!

Here's how I'm currently handling many-to-many. This is for an app that tracks records for students. Each student can have many records (like book reading progress or read an internet resource). Also, one record can apply to many students (such as when two students complete the same reading assignment).

enum Subject {
  SCIENCE
  MATH
  LANGUAGE
  SOCIAL_STUDIES
  PHYS_ED
  HEALTH
  ART
  MUSIC
  RELIGION
  FOREIGN_LANGUAGE
}

type Student @model @auth(rules: [{allow: owner}]) {
  id: ID!
  firstName: String!
  lastName: String!
  birthdate: AWSDate!
  bookMaps: [BookMap]! @connection(name: "StudentBooks")
  resourceMaps: [ResourceMap]! @connection(name: "StudentResources")
}

interface Record  {
  id: ID!
  week: String!
  subjects: [Subject]
}

type BookMap @model(queries: null) @auth(rules: [{allow: owner}]) {
  id: ID!
  bookRecord: BookRecord! @connection(name: "BookStudents")
  student: Student! @connection(name: "StudentBooks")
}

type BookRecord implements Record @model @auth(rules: [{allow: owner}])  {
  id: ID!
  week: String!
  subjects: [Subject!]!
  studentMaps: [BookMap!]! @connection(name: "BookStudents")
  title: String!
  progressStart: Int!
  progressEnd: Int!
}

type ResourceMap @model(queries: null) @auth(rules: [{allow: owner}]) {
  id: ID!
  resourceRecord: ResourceRecord! @connection(name: "ResourceStudents")
  student: Student! @connection(name: "StudentResources")
}

type ResourceRecord implements Record @model @auth(rules: [{allow: owner}])  {
  id: ID!
  week: String!
  subjects: [Subject!]!
  studentMaps: [ResourceMap!]! @connection(name: "ResourceStudents")
  location: String!
  notes: String
}

And here's what full nested queries look like. Not super clean, eh? :)

query {
  listStudents {
    items {
      id
      firstName
      bookMaps {
        items {
          bookRecord {
            id
            title
          }
        }
      }
    }
  }
  listBookRecords {
    items {
      id
      week
      title
      studentMaps {
        items {
          student {
            firstName
          }
        }
      }
    }
  }
}

[phillip055]

++

[sprucify]

So what would be the mutation call if I want to create a new blogpost and also create and attach the associated comments. Or do I have to fire multiple mutation calls to realize this?

[kevin-lindsay-1]

+1 on this as well, from the perspective of someone evaluating options, this particular issue presents as a pain point.

[sirshannon]

In the future we will support many to many out of the box and will manage setting up the join model for you and configuring better names for the mutations that relate objects.

How long until The Future? I'm ready for my flying car but I need many-to-many.

[AndrewKvalheim]

[…] I had one of these setup at one point and it was working. I thought I didn't need it anymore so I removed it. Now I'm going to add it back again and I'm getting the error below. […]

Only one resolver is allowed per field. (Service: AWSAppSync; Status Code: 400; Error Code: BadRequestException; Request ID: 06480039-0ade-11e9-aca0-3707b99c7ce2

This workaround resolved this for me without having to recreate the AppSync API.

[javiertc]

Hi @mikeparisstuff, I have a concern with something, according to Rich Houliah in this video about DynamoDB best practices https://www.youtube.com/watch?v=HaEPXoXVf2k&t=3184s, he is recommending to use only one table and an adjacency list model for the many to many relationships. But from what I see here the intention with Amplify is to create a separate join table, something that he completely refuses in his talk. I'm already using this model in my App and I'm afraid that I won't be able to use amplify in the future because of it. Is there any chance to be aligned with DynamodDB best practices?

[pranavgawri]

Future awaits! this is a very seriously missing piece, when can we expect this?

[FeedMyTummy]

@mikeparisstuff When I get a user, I have have access to the list of posts. But, how can I get those posts to be returned sorted by a specific field?

[tafelito]

What would be the best way to do self-relations?

Something like this

type User @model {
  id: ID!
  username: String!
  friends: [User] @connection(name: "UserFriends")
}

this is actually not a many-to-many, but when I try to do that, that's what the cli says, Many to Many connections are not yet supported.

related to aws-amplify/amplify-category-api#435

[michaelcuneo]

@flybayer ... how do you get information in and out of this though? I have a similar Many-to-many setup, and I can't get anything to link up. I can create Projects, I can create Staff, I can create ProjectStaff (A many to many linking the two), but Staff ProjectStaff is null, and Project ProjectStaff is null. I've tried adding a single item to say ... Staff. A single item to Project. Then I see that ProjectStaff link in both is null... so I create a ProjectStaff, then add the ID from the ProjectStaff as the ID to collaborators in Project, but that won't work either because it requires an Array or them. It's like Holy Hell... In one area of my project, I do like 8 GraphQL calls to do one thing... isn't this the opposite of GraphQL's purpose?

type Project @model {
  id: ID!
  title: String!
  details: String!
  category: [String!]
  gallery: Gallery @connection(name: "ProjectGallery")
  collaborators: [ProjectStaff] @connection(name: "ProjectCollaborators")
}

type ProjectStaff @model {
  id: ID!
  project: Project! @connection(name: "ProjectCollaborators")
  staff: Staff! @connection(name: "StaffCollaborators")
}

type Staff @model {
  id: ID!
  name: String!
  firstName: String!
  lastName: String!
  mugshot: String!
  title: String!
  bio: String!
  email: String!
  website: String!
  cognitoID: ID
  group: String!
  projects: [ProjectStaff] @connection(name: "StaffCollaborators")
}

type Image @model {
  id: ID!
  key: String!
  mime: String!
  gallery: Gallery @connection(name: "ImageList")
}

type Gallery @model {
  id: ID!
  name: String!
  project: [Project] @connection(name: "ProjectGallery")
  images: [Image] @connection(name: "ImageList")
}

[marcschroeter]

Guys we need urgently your help with a many-to-many relationship.

We followed this thread and followed the documentation. Maybe its the same problem from @michaelcuneo

In our case, we have a many-to-many relationship between cities and benefits. Many Cities can be assigned to one benefit and many benefits can be assigned to one city.

We have the following schema:

type City @model {
    id: ID!
    name: String!
    benefits: [CityBenefit] @connection(name: "cityBenefit")
}
type Benefit @model {
    id: ID!
    name: String!
    category: [Category] @connection
    company: String!
    description: String!
    website: String
    email: String
    phone: String
    address: String
    global: Boolean!
    cities: [CityBenefit] @connection(name: "benefitCity")
}
type Category @model {
    id: ID!
    name: String!
}
type CityBenefit @model {
    id: ID!
    city: City @connection(name: "cityBenefit")
    benefit: Benefit @connection(name: "benefitCity")
}

1. After adding a city and a benefit, we can’t get this information in and out. the city in CityBenefit is null and same for benefit. Its the same problem from @michaelcuneo

2. With this model, it's still not possible to filter for all benefits in a given city.

   @mikeparisstuff @flybayer you can please help us? What is missing or wrong exactly?

[flybayer]

@michaelcuneo @marcschroeter it sounds like you can create the joins fine, but you can't read them? In my previous post I show the necessary query to get all the data. Make sure you have all the items in the correct spots. If you skip items somewhere, you will get a null result.

[michaelcuneo]

So when you do a query in your case... with your schema, what is Items. under BookMaps and StudentMaps, is this a query that you've custom written into the schema, or is this all autogenerated.

When I create a student in your case I'd assume the following. I create the Student with firstName, lastName, birthday, ... and that's all ... because bookMaps doesn't exist at this point... right. Then I create a BookRecord, with week, subjects, title, progressStart, progressEnd,... and that's all... because studentMaps doesn't exist at this point either... yes?

Now, I need to create a BookMap so that the two are linked... when I create the items, for this, do I provide the entire object as in...

type BookMap @model(queries: null) @auth(rules: [{allow: owner}]) {
  id: ID!
  bookRecord: BookRecord! @connection(name: "BookStudents")
  student: Student! @connection(name: "StudentBooks")
}

Is bookRecord the ID! of a bookRecord, or is it the entire bookRecord object. I assume it's an ID! yes? When I provide an ID! to a mapped connection in this manner, I do get data back in the connection, but it's a nextToken, which I don't know what to do with.

Am I meant to create all of the records first then mutate update them with correct data... or am I meant to provide every single part of the tree all at once? I'm calling about 6 GraphQL Operations to do one thing right now... creating Student, Creating Book Record, Creating BookMap,... is this really correct?

[michaelcuneo]

@flybayer @marcschroeter I have found my issue... Creating and retrieving is limited by the Tree Depth, the importance of this isn't really discussed in the documentation. I did see this asked once when setting up the GraphQL Schema initially, but didn't know what it referred to...

After dropping out of amplify serve, and running amplify codegen --max-depth 4 ... I'm now able to go further into my schema and get real data, with items, instead of NULL.

But... further to this, it's only solving half my problem, The relationships are still not being created properly. But I can access my image galleries and images.

[michaelcuneo]

Looks as though, if I create everything in the right order, it will successfully work as planned, I just have to ensure that I rerun amplify codeine --max-depth and change this to numbers way bigger than I had assumed... for some reason, something like Projects -> Staff -> Projects -> Items, to me that looks like 2 depth, but it's larger than 4? To go past Items into an array, which is only meant to be a singular, adds another to the list... so yeah. I've been adding --max-depth 10 now just to make sure I don't keep hitting nulls as I progress.

@marcschroeter

Add your City... Add your Benefit... then create a CityBenefit, supplying the entire object structure of the City and Benefit to the CityBenefit... not just it's ID's. And add them one by one... not all at once. I loop through mine as a map. So it adds a project/staff member, another project/staff member, another project/staff member... each time Project might be the same, Staff member might change, or Staff member might change, and Project is different... depending on the circumstance, it'll link them all up and provide the link back to the Project / Staff in the applicable areas. I have been at this all day and can guarantee now that it works, after these small changes.

After this though, you will still get null because of the depth issue... change your depth and see if they are no longer null.

[OukhouyaAyoub]

Hello, I have a question about many-to-many relationship 👍

type City @model {
    id: ID!
    name: String!
    benefits: [CityBenefit] @connection(name: "cityBenefit")
}
type Benefit @model {
    id: ID!
    name: String!
    category: Category @connection(name: "BenefitCat")
    company: String!
    description: String!
    website: String
    email: String
    phone: String
    address: String
    global: Boolean!
    cities: [CityBenefit] @connection(name: "benefitCity")
}
type Category @model {
    id: ID!
    name: String!
    benefit: [Benefit] @connection(name: "BenefitCat")
}
type CityBenefit @model {
    id: ID!
    city: City @connection(name: "cityBenefit")
    benefit: Benefit @connection(name: "benefitCity")
}

and the query :

    listBenefits{
        items{
            id
            name
            category{
              id
              name}
            city{
              items{
                city {
                  id
                  name
                }
        }}}
    }
}

I am able to query for example a benefit and its category and city, my question is : is it possible to filter a benefit per city and category ? It did work to filter per city alone but both I don't know if it's possible Do you have an idea please ?

[AlessandroAnnini]

Hi, I'm sorry, is there any ETA about this?

[smisaacs]

++

[matthieunelmes]

Has anyone managed to solve many-to-many self-relations? For instance users with friends.

My current attempt is:

type User @model {
  id: ID!
  email: String
  name: String
  friends: [UserFriend] @connection(name: "UserFriends")
}

type UserFriend @model {
  id: ID!
  user: User! @connection(name: "UserFriends")
  friend: User! @connection(name: "UserFriends")
}

But I'm getting CREATE_FAILED UserFriendTable AWS::DynamoDB::Table Tue Apr 16 2019 23:15:21 GMT+0100 (British Summer Time) Property AttributeDefinitions is inconsistent with the KeySchema of the table and the secondary indexes

[michaelcuneo]

@matthieunelmes that doesn't look right at all... you need a friend model.

type User @model { id: ID! email: String name: String friends: [UserFriend] @connection(name: "UserFriendFriend") }

type UserFriend @model { id: ID! user: User! @connection(name: "UserFriendUser") friend: Friend! @connection(name: "UserFriendFriend") }

type Friend @model { id: ID! name: String user: [UserFriend] @connection(name: "UserFriendUser") }

... See the User is having Many friends, and the Friend is having many users, but because they can't be directly linked to each other, they're linked to a UserFriend. So for every Friend there can be many UserFriends, and for every User there can be many UserFriends, and each link will be one link.

After you successfully create the resources though, you must ensure that you're running amplify codegen --max-depth ... with a --max-depth higher than 2. in my particular instance I need at least 7. Which is probably stupid, but it saves me from multiple calls.

When I say, mutate for listUsers, I'd get. Level 1... User, Level 2... items, Level 3... Friends, Level 4... items, Level 5... User, Level 6... items, Level 7... etc...

So you're on at least --max-depth 7 to receive everything up until the items of the users within friends of users.

[matthieunelmes]

@michaelcuneo so, If I understand that setup correctly. To create a link between two users I'd need to insert two items.

One with userFriendUserId and userFriendFriendId into UserFriend then friendUserId into Friend?

Wow, what a tongue twister!

Assuming the name parameter is redundant on the Friend table

[michaelcuneo]

Are you trying to make friends as a link of friends... ? If so then you could just have the friend model house the ID of the user that should be the friend, instead of extending that to include names or anything, but yes... it's a bit confusing at first.

Friend doesn't even need name... because the User that it links to has a name, it will just be 'id: id!, and user: [UserFriend] @connection(name: "UserFriendUser")...

In your case to add a new friend relationship, you never need to look at 'Friends' you use Users and UserFriend... to make the relationship, just make a UserFriend ... and provide it two ID's... of two different users.

In the following case I'm creating many friends for one user. I just feed it the user, and all of the friends.

async function handleCreateConnection(user, userFriends) {
  return userFriends.map(collaborator =>
    API.graphql(
      graphqlOperation(createUserFriend, {
        input: {
          UserFriendUser: firstUser.id,
          UserFriendFriend: secondUser.id,
        },
      }),
    )
      .then(result => result)
      .catch(err => err),
  );
}

Later on when I call mutation on the single user... it'll have something like this...

Users: {
  items: { 
    id: 1,
      email: blah,
      name: blah,
      friends: {
        items: {
          id: 3,
          email: blah,
          name: friend one,
        },
        {
          id: 7,
          email: blah,
          name: friend two,
        },
     },
  },
},

Easy.

[michaelcuneo]

That's only with the assumption that you cannot link a many to many to a single model. I'm pretty sure you cannot.

[matthieunelmes]

I can successfully create a many-to-many with the @connection approach above which requires 1 joining table. The issue arises when I'm trying to create a self-relating connection. So a User can have multiple friends which are of the type User

[michaelcuneo]

Yeah a relation to self doesn’t work does it? That wouldn’t even make sense? Or you’d end up with Users with Users in it. Sounds sensible in a lateral sense, but logically it would break it.

[matthieunelmes]

Hmmm, the schema compiles but it's not pushing to Amplify.

I'm getting Cannot perform more than one GSI creation or deletion in a single update

[janpapenbrock]

@michaelcuneo @matthieunelmes would you mind discussing that issue somewhere else, e.g. in a separate issue here or on StackOverflow? I feel it's bloating this thread without adding a lot to the topic at hand, which is how to implement Many-to-Many natively in Amplify.

[matthieunelmes]

@janpapenbrock but this is about how to add many-to-many natively within Amplify. As https://github.com/aws-amplify/amplify-cli/issues/91#issuecomment-419226819 is the only given solution but in this instance, we're discussing a self-relating join which was mention also in this thread https://github.com/aws-amplify/amplify-cli/issues/91#issuecomment-475978115

Creating another issue would just be creating a duplicate of what's already being discussed here

[michaelcuneo]

My answers to @Matthieunelmes were directly related to implementing Many to Many. The questions relating to many to many with self referencing is also directly related to implementing Many to Many. If Matthieunelmes wants to repost in a seperate thread I’ll respond accordingly.

[phaniteja1]

You can implement many to many yourself using two 1-M @connections and a joining @model. For example:

type Post @model {
  id: ID!
  title: String!
  editors: [PostEditor] @connection(name: "PostEditors")
}
# Create a join model and disable queries as you don't need them
# and can query through Post.editors and User.posts
type PostEditor @model(queries: null) {
  id: ID!
  post: Post! @connection(name: "PostEditors")
  editor: User! @connection(name: "UserEditors")
}
type User @model {
  id: ID!
  username: String!
  posts: [PostEditor] @connection(name: "UserEditors")
}

You can then create Posts & Users independently and join them in a many-to-many by creating PostEditor objects. In the future we will support many to many out of the box and will manage setting up the join model for you and configuring better names for the mutations that relate objects. In the future we will also support using batch operations to create bi-directional relationships like friendships that may have some qualifying information on the joining model itself.

How can I filter on PostEditor for a specific User and Post? Thanks!

[artista7]

@ev-dev This is exactly the type of behavior that will be supported in the future. There is a TODO here https://github.com/aws-amplify/amplify-cli/blob/master/packages/graphql-connection-transformer/src/ModelConnectionTransformer.ts#L132 that is waiting to be worked on and will automatically handle hooking up the join table and additional mutations to enable the many to many.

@mikeparisstuff @kaustavghosh06 Until when can we expect this feature? This piece is so central to database design.

[helloniklas]

@michaelcuneo I'm attempting to set up the User and UserFriend, but it then complains about "InvalidDirectiveError: Found one half of connection "UserFriendUser" at UserFriend.user but no related field on type User"

Is you examples correct?

[matthieunelmes]

@helloniklas the concept works, but unfortunately, it cannot currently be handled by the amplify CLI.

I managed to get it working by firstly creating the basic models such as:

type User @model {
  id: ID!
  email: String
  name: String
  profile_pic: String
  friends: [UserFriend]
}

type UserFriend @model{
    id: ID!
    user: User! 
    friend: User! 
}

Then manually creating the resolvers through the AppSync dashboard in the schema editor. It took a lot of fiddling, and I believe you can actually get it to work through the CLI if you add one @connection at a time.

[sebastienfi]

@matthieunelmes Watch out as your manual changes will be overridden by amplify push.

[matthieunelmes]

@sebastienfi I'm aware of this. Hence why I keep a backup of the code gen'd schema.graphql it's far from ideal but the only way to get it to work.