Amplify-cli: Filter on a Many-To-Many Connections

[LpmRaven]

Is it possible to filter on a many-to-many connection type?

I am trying to query my products, filtered by categories and subCategories. My products can have multiple categories and multiple subCategories. With my current schema below I cannot query my products and filter by categories or subcategories. The filters don't exist.

I tried to add a "categorieIds" and "subCategorieIds" to the product type but "All fields provided to an @connection must be non-null scalar or enum fields" so that simply is not possible when you have multiple categories and subCategories.

Any suggestions would be much appreciated!

schema.graphql

type Product @model
  @key (fields: ["globalIdColor"])
  {
  name: String!
  formattedName: String!
  slug: String!
  globalId: String!
  globalIdColor: String!
  categories: [ProductCategory!]! @connection(keyName: "categoryByProduct", fields: ["globalIdColor"])
  subCategories: [ProductSubCategory!]! @connection(keyName: "subCategoryByProduct", fields: ["globalIdColor"])
  updatedAt: AWSDateTime!
  createdAt: AWSDateTime!
}

type Category @model
  @key(fields: ["slug"])
  {
  name: String!
  formattedName: String!
  slug: String!
  products: [ProductCategory!]! @connection(keyName: "productByCategory", fields: ["slug"])
}

type SubCategory @model
  @key(fields: ["slug"])
  {
  name: String!
  formattedName: String!
  slug: String!
  products: [ProductSubCategory!]! @connection(keyName: "productBySubCategory", fields: ["slug"])
}

# join model
type ProductCategory @model(queries: null)
  @key(fields: ["productId", "categoryId"])
  @key(name: "categoryByProduct", fields: ["productId", "categoryId"])
  @key(name: "productByCategory", fields: ["categoryId", "productId"]) {
  productId: String!
  categoryId: String!
  product: Product! @connection(fields: ["productId"])
  category: Category! @connection(fields: ["categoryId"])
}

# join model
type ProductSubCategory @model(queries: null)
  @key(fields: ["productId", "subCategoryId"])
  @key(name: "subCategoryByProduct", fields: ["productId", "subCategoryId"])
  @key(name: "productBySubCategory", fields: ["subCategoryId", "productId"]) {
  productId: String!
  subCategoryId: String!
  product: Product! @connection(fields: ["productId"])
  subCategory: SubCategory! @connection(fields: ["subCategoryId"])
}

Here is an example of the product query.

 const productQuery = `
    query ($nextToken: String){
        appSync {
            listProducts (
               filter: {
                       // filter by categories and subCategories
               }
               nextToken: $nextToken
            ){
                items {
                    name
                    formattedName
                    slug
                    globalId
                    globalIdColor
                    updatedAt
                    createdAt
                    categories {
                        items {
                            category {
                                name
                                formattedName
                                slug
                            }
                        }
                    }
                    subCategories {
                        items {
                            subCategory {
                                name
                                formattedName
                                slug
                            }
                        }
                    }
                }
                nextToken
            }
        }
    }`

[kaustavghosh06]

Closing this issue as a duplicate of #1039
We have added this to our backlog as a feature request/enhancement.

[LpmRaven]

@kaustavghosh06 I don't think the solution is a new feature. I was modelling my database for SQL. The answer is to correctly model the data for a NoSQL database. I expect I won't be the only person that is new to NoSQL so it is important to tell people the correct way to structure their data when using AppSync and DynamoDB https://www.youtube.com/watch?v=HaEPXoXVf2k

Here's my schema.graphql setup for NoSQL with AppSync and DynamoDB. I query all my data from the product table. I use the secondary Indexes to query by category and subCategory. You really need to build a NoSQL database knowing how you will query the data.

type Category @model
  @key(fields: ["slug"])
  {
  name: String!
  formattedName: String!
  slug: String!
  products: [Product!]! @connection(name: "productCategory", fields: ["slug"])
}

type SubCategory @model
  @key(fields: ["slug"])
  {
  name: String!
  formattedName: String!
  slug: String!
  products: [Product!]! @connection(name: "productSubCategory", fields: ["slug"])
}

type Product @model
  @key (fields: ["globalIdColor", "region", "categorySlug", "subCategorySlug", "size", "supplier"])
  # Indexes for pages
  @key(name: "byCategory", queryField: "productsForCategory", fields: ["region", "categorySlug"])
  @key(name: "bySubCategory", queryField: "productsForSubCategory", fields: ["region", "categorySlug", "subCategorySlug"])
  @key(name: "byAward", queryField: "productsForAwards", fields: ["region", "categorySlug", "subCategorySlug", "awardSlug"])
  {
  globalIdColor: String!
  productVarient: Boolean!
  region: Region!

  # Category Info
  categorySlug: String!
  category: Category! @connection(name: "productCategory", fields: ["categorySlug"])
  subCategorySlug: String!
  subCategory: SubCategory! @connection(name: "productSubCategory", fields: ["subCategorySlug"])

  # Price/Size/Supplier
  supplier: String!
  size: String!

  # Metadata
  updatedAt: AWSDateTime!
  createdAt: AWSDateTime!
}

[RossWilliams]

@LpmRaven you could even remove "byCategory" and use "bySubCategory" alone. If you want all items in the category, use a "beginsWith" sort, otherwise for subcategories use "eq".

[LpmRaven]

@LpmRaven you could even remove "byCategory" and use "bySubCategory" alone. If you want all items in the category, use a "beginsWith" sort, otherwise for subcategories use "eq".

@RossWilliams Yes, thanks for that!

I would suggest that the documentation for amplify is misleading people to design for DynamoDB as if it was for SQL. After watching the DynamoDB Deep Dive by Rick Houlihan, I realised I was thinking about it all wrong way.

[RossWilliams]

@LpmRaven Mike Paris has commented in the issues previously that the amplify model design was deliberate because it offered more flexibility in evolving a schema vs. a single table design. The deep dive video is very helpful for amplify users as well, but I'm curious if you have specifics on which sections you would want to change and why.

[LpmRaven]

@RossWilliams It is just odd when reading the Amplify docs and reading the DynamoDB docs. The DynamoDB docs state that most well designed applications require only one table, unless there is a specific reason for using multiple tables." It says that multiple times across its docs and really pushes that DynamoDB should be used with that design.

On the other hand, looking at the Amplify docs, you would be forgiven if you thought it was docs about using a different DB. There is no mention of single table design and it constantly talks about relational data structures. I'm sure there are reasons behind doing that but there is a lack of consistency and it is confusing. From what I have experienced so far, designing the DB in the way that the Dynamo docs describe works for my project.

A specific example I would point to might be the Data Access Patterns. I used this when first designing my schema, there should definitely be an example of the same scenario but with a single table design.

Yourself and Mike will definitely have a better understanding of Dynamo than myself so I'm sure you will be able to understand what I'm trying to say. If you get to a stage where you are using many-to-many connections, you are going to have problems querying data and should look at a single table design. DynamoDB was not made for connections, it was made for no joins.