Amplify-cli: [GraphQL Transform] Filtering with multiple many-to-many relationships

[ialmoqren]

* Which Category is your question related to? *
GraphQL Transform

* What AWS Services are you utilizing? *
Appsync, Amplify

* Provide additional details e.g. code snippets *
If I have a schema like this

type Post @model {
  id: ID!
  title: String!
  editors: [PostEditor] @connection(name: "PostEditors")
  categories: [PostCatoegory] @connection(name: "PostCategories")
}

type PostEditor {
  id: ID!
  post: Post! @connection(name: "PostEditors")
  editor: User! @connection(name: "UserEditors")
}
type User @model {
  id: ID!
  username: String!
  posts: [PostEditor] @connection(name: "UserEditors")
}

type PostCatoegory {
  id: ID!
  post: Post! @connection(name: "PostCategories")
  category: Category! @connection(name: "CategoryType")
}
type Category @model {
  id: ID!
  categoryName: String!
  posts: [PostCatoegory] @connection(name: "CategoryType")
}

This is how I can get the posts' titles that are posted by the user "ibrahim"

    query {
        listUsers(
        filter:{
          username:{
            eq: "ibrahim"
          }
        })
      {
        items{
          posts{
            items{
              post{
                title
              }
            }
          }
        }
      }
    }

And I will do a similar thing to get the posts which have a specific categoryName, but my question is how can I get the posts that are posted by let's say "ibrahim" and have a categoryName "art" ?

I have spent a lot of time trying to solve this issue and will appreciate your help

[mikeparisstuff]

Hey @ialmoqren thanks for the question. We have an RFC coming out the details some new features around custom indexes that will give you more control over how the tables created by @model are configured. In general, to find the posts that are posted by "ibrahim" and that have a categoryName "art" will require an index on the "Post" table that lies on top of these two attributes.

For example, if I have a table "Post" with a GSI with a hash key of "userId" and a sort key of "categoryName" then I can do a DynamoDB query operation against that table to find all the posts with that "userId" and "categoryName". I will tag this issue when the custom indexes RFC is released so we can discuss if it will solve your use case.

[ialmoqren]

Thanks @mikeparisstuff a lot for your response. I obviously need to learn more about DynamoDB's GSIs, but please allow me to ask you this, should those indexes be created through aws console (website) or can I declare them directly from my schema.graphql.

I couldn't find resources about how to create DynamoDB's GSIs when using Amplify, I would really appreciate it if you can suggest me any so I can manage to solve my issue.

I can see that it's possible to specify the keyField and sortField on a connection, but in my case it's two connections not one. If that's what you mean.

Many thanks for your time, I understand how busy you are.

[mikeparisstuff]

@ialmoqren We are going to add support for custom indexes via the schema.graphql. We have an RFC open here https://github.com/aws-amplify/amplify-cli/issues/1062 and would love to hear your feedback on the design.

[ialmoqren]

COPIED FROM #1062

@mikeparisstuff Thanks for your time and effort, but I believe none of those changes would tackle this issue,

Please let me re-explain my issue,

Let's say I have a "Post" type as following:

type Post @model {
  id: ID!
  title: String!
  editors: [PostEditor] @connection(name: "PostEditors")
  categories: [PostCatoegory] @connection(name: "PostCategories")
  likes: [PostLike] @connection(name: "PostLikes")
}

The "editors", "categories" and "likes" have MANY-TO-MANY relationships with "Writer", "Category" and "Reader" types respectively.

I can easily get:

• The posts someone wrote
• The posts in one category
• The posts someone liked

with something like this:

    query {
        listWriters(
        filter:{
          name:{
            eq: "X"
          }
        })
      {
        items{
          posts{
            items{
              post{
                title
              }
            }
          }
        }
      }
    }

But, my issue is how can I combine these filters, I need to get the posts that are written by the writer X, and in the category Y and are liked by the reader Z.

I believe this use case is very common in many apps and websites, think about things like marketplaces which have many filters based on many criteria.
My example has three filters but you can think of any number of filters someone may have.

It's possible (and I hope) that the issue is much simpler than how I am thinking about it.

I really appreciate your effort.

[stanleycyang]

@ialmoqren at this moment, I don't believe you are able to filter many-to-many relationships. I'm running into the same issue where we have a many-to-many relationship:

type User
  @model
{
  id: ID!
  projects: [ProjectUser] @connection(name: "UserMembers", sortField: "createdAt")
  createdAt: AWSDateTime
}

A ProjectUser model to join the two:

type ProjectUser @model(queries: null) {
  id: ID!
  project: Project! @connection(name: "ProjectMembers")
  member: User! @connection(name: "UserMembers")
  createdAt: AWSDateTime
}

type Project
  @model
{
  id: ID!
  title: String!
  members: [ProjectUser] @connection(name: "ProjectMembers")
  createdAt: AWSDateTime
}

And when it gets created, I notice that the input filter looks like this:

input ModelProjectUserFilterInput {
    id: ModelIDFilterInput
    createdAt: ModelStringFilterInput
    and: [ModelProjectUserFilterInput]
    or: [ModelProjectUserFilterInput]
    not: ModelProjectUserFilterInput
}

Which is severely limited in filtering the information necessary. This obviously is a huge limitation with the many-to-many relationship and needs to be addressed somehow. At the very least, we should be able to filter by project ID or user ID in the model, the ProjectUser ID is almost useless as a filter option.

[kaustavghosh06]

@ialmoqren We launched support for custom indexes today. You can find docs for the same out here - https://aws-amplify.github.io/docs/cli/graphql#key
Please let us know if you're still not able to solve your problem through this solution and we'll re-open this issue for you.

[davidfarinha]

@kaustavghosh06 I don't believe custom indexes will solve this problem.
I'm facing a related and very common problem, I need to filter a many-to-many connection (which in my case is a list of User's friends) by fields defined on the user:

type User @model @searchable {
    id: ID!
    userName: String!
    firstName: String!
    lastName: String
    email: String
    phoneNumber: String
    handle: String
    isInitialized: Boolean!
    updatedAt: String
    createdAt: String
    friends: [UserFriend] @connection(name: "UserFriends")
    friendsOf: [UserFriend] @connection(name: "FriendUsers")
}
type UserFriend @model @searchable {
  id: ID!
  user: User! @connection(name: "UserFriends")
  friend: User! @connection(name: "FriendUsers")
}

This doesn't allow me to filter on any fields defined on UserFriends.User, through the @connection attribute (eg filtering all user's friends by user's with a certain username, name, email etc). The generated graphql input type is:

export type SearchableUserFriendFilterInput = {
  id?: SearchableIDFilterInput | null,
  and?: Array< SearchableUserFriendFilterInput | null > | null,
  or?: Array< SearchableUserFriendFilterInput | null > | null,
  not?: SearchableUserFriendFilterInput | null,
};

Making any kind of nested filtering impossible. Does anyone know of any temporary workaround for this?

[onerider]

https://github.com/aws-amplify/amplify-cli/issues/2598
A thread I started a couple of weeks ago while starting to use Appsync and I see no way to do filtering over many-to-many relations.

[yuth]

Re-opening per https://github.com/aws-amplify/amplify-cli/issues/2598#issuecomment-557847223. Please comment on #2598 if thats the accurate use case

[LpmRaven]

I have another use case for this exact issue. #3003 Has there been any progress on filtering many-to-many connections?

[avlonder]

I agree that filtering over many-to-many relations is not possible at the moment even though it has a lot of use cases.
For example, in my case, I have a many-to-many friendship relation between users. Each user has a status field. Currently, there is no straightforward way to query the friends of a specific user with status "active". Is this kind of functionality in the queue?

PS: The obvious workaround is to put a copy of the status field belonging to a certain User item in each of the associated Friendship items (which is the joint table), but this undermines the "single source of truth" principle.

[ajnunez16]

I agree with @avlonder that the use of join table in the amplify docs goes against the single table best practice in DynamoDB docs.