Amplify-cli: Query with filter over many-to-many relationship

[onerider]

I have two items, Product and Category with a many-2-many relationship (built using Amplify)
In the dynamodb this is mapped like this:

Product >- ProductCategory -< Category

All good, I can for example query on a product and see all categories it is part of and vice versa.

However, I don't get filtering to work and I am not even sure if this really is supported.
What I want to do is query for Products which are part of either Category X or Category Y.

Is such a graphql query supported? If not, what is a recommended approach? I am thinking of a custom query calling a lambda as plan B

Thanks in advance

[RossWilliams]

You have your mapping table ProductCategory, you want to get all products in X or Y

type ProductCategory @model @key(name: "byCategory", fields: ["categoryId", "productId"], queryName: "productsByCategory"]) {
  id: ID!
  productId: ID!
  categoryId: ID!
  product: Product! @connection(keyField: "productId")
  category: Category! @connection(keyField: "categoryId")
}

you can make two queries in one request:

query {
  categoryX: productsByCategory(categoryId: "X") {
    items {
      ...responseFragment
    }
  }
  categoryY: productsByCategory(categoryId: "Y") {
     items {
       ...responseFragment
     }
  }
}

[SwaySway]

@onerider If you are looking to do a query for two categories the query snippet above would be the way to do it.
If you have any other questions related to this please feel free to comment below.

[onerider]

Thanks for the response, this got me further. However, I now need to filter on the products.

A real life use case would be "Give me all products where name contains 'computer' from the categories X and Y.

Would be awesome with an explanation on how to do this and I imagine this to be a very common use case so I'm surprised my googling is letting me down :)

[RossWilliams]

type ProductCategory @model @key(name: "byCategory", fields: ["categoryId", "productName"], queryName: "productsByCategorySortedByName"]) {
  id: ID!
  productId: ID!
  categoryId: ID!
  productName: String!
  product: Product! @connection(keyField: "productId")
  category: Category! @connection(keyField: "categoryId")
}

query {
  categoryX: productsByCategorySortedByName(
      categoryId: "X",
      productName: { equals: "computer"}
     ) {
    items {
      ...responseFragment
    }
  }
}

[onerider]

Coming from an SQL background this is taking some getting used to :)
So basically, the join-table would be the "master" table for querying and any searchable field from the tables it is joined to, would be duplicated in this table. I believe this will solve my problem. Many thanks!

[onerider]

Ok, now I have landed in the conclusion that this isn't properly implemented, using a join table.

1. The join-table needs to duplicate every value in the other two tables if you want to filter on any field in those two tables
2. If I want to search for products, filter on both product values as well as categories using a join-table with duplicated values, I end up getting duplicate products in the response if a product has multiple categories.
   As far as I understand, if I need to search for products and filter on categories, I will need the product table to hold a collection of categories.
   Is my reasoning incorrect? Thanks

[yuth]

@onerider is this the type of behavior you're looking for

type Book @model {
    id: ID!
    title: String!
    authors: [AuthorBook] @connection(keyName: "byBook", fields: ["id"])
}

type Author @model {
    id: ID!
    firstName: String!
    lastName: String!
    books: [AuthorBook] @connection(keyName: "byAuthor", fields: ["id"])
}

type AuthorBook
    @model
    @key(name: "byAuthor", fields: ["authorId"])
    @key(name: "byBook", fields: ["bookId"]) {
    id: ID!
    authorId: ID!
    bookId: ID!
    author: Author @connection(fields: ["authorId"])
    book: Book @connection(fields: ["bookId"])
}

and support querying author by last name

query getBook {
    getBook1(id: "bookid") {
        id
        title
        authors(filter: { lastName: { eq: "Doe" } }) {
            items {
                author {
                    id
                    firstName
                    lastName
                }
            }
        }
    }
}

[onerider]

@onerider is this the type of behavior you're looking for
@yuth In your example above you have a specific book and if this book have many authors it will only show "Doe".
I want to filter on Books containing the word "Story" in the title and having an author with the Last Name "Doe" and a Publisher named "Star Books"
So three entities, Book, Author and Publisher

[RossWilliams]

@onerider unfortunately for DynamoDB you need to be specific about your query to design the correct layout and API calls.
To restate what you probably are asking: Return a list of books where title contains "story", Author Last name equals "Doe" and Publisher equals "Start Books".

To use DynamoDB to solve the issue, the main difficulty is that it sounds like you may want to search against an arbitrary list of fields, and search against multiple fields using a "contains" filter. I can tell you how to solve the specific query stated above, but it will not be easily adaptable to other queries. This is where elasticsearch through the @seachable directive may be the best option.

The DynamoDB option for your specific query is to add a @key to the book model with fields: ["Publisher","AuthorLastName"], and query with a limit that is high and filter: {title: {contains: "story" } }. The reason you need a high limit is that the filter happens after the limit, the reverse of SQL.

[onerider]

Thanks @RossWilliams
I think you're right about the use of Elasticsearch to simplify things. Currently for the demo application we're building we decided to go with MongoDb which seems to have chosen a different approach to support N:M relations. In this approach we don't have any join-table. Instead, Book has an array with Author Object ID's and in the same manner Author has an array with Book Object ID's.
There are drawbacks to this approach as well, sure.

[LpmRaven]

I have just opened a similar issue #3003 Trying to query my products, filtered by categories and subCategories. I have provided my schema. This seems really similar to your original example @onerider and I would appreciate anything you have learned.

[kaustavghosh06]

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

[Blkc]

After some experimentation (because I am cheap and didn't really want to have a elasticsearch instance running on dev) I realize that if you don't need to reuse (ie. Category in this case), then you can use listProducts() (should be auto generated by amplify) with the following schema and filter

type Product @model
  @key(fields: ["id", "createdAt"]) {
  id: ID!
  name: String!
  createdAt: AWSDateTime!
  categories: [String]
}

{
  "filter": {
    "or": [
        { "categories": {"contains": "X"} },
        { "categories": {"contains": "Y"} }
    ]
  },
  "sortDirection": "DESC"
}

This will then give you products that are within category X or category Y sorted on createdAt in descending order. Hope it helps someone looking to save cost in the future during development.

[LpmRaven]

After some experimentation (because I am cheap and didn't really want to have a elasticsearch instance running on dev) I realize that if you don't need to reuse (ie. Category in this case), then you can use listProducts() (should be auto generated by amplify) with the following schema and filter

type Product @model
  @key(fields: ["id", "createdAt"]) {
  id: ID!
  name: String!
  createdAt: AWSDateTime!
  categories: [String]
}

{
  "filter": {
    "or": [
      { "categories": {"contains": "X"} },
        { "categories": {"contains": "Y"} }
    ]
  },
  "sortDirection": "DESC"
}

This will then give you products that are within category X or category Y sorted on createdAt in descending order. Hope it helps someone looking to save cost in the future during development.

Unfortunately, this won't work with more than X number of products. (X being the number in a paginated result). The filter will only filter out results on the current pagination. So you don't get back results like you would with elasticsearch.

[Blkc]

After some experimentation (because I am cheap and didn't really want to have a elasticsearch instance running on dev) I realize that if you don't need to reuse (ie. Category in this case), then you can use listProducts() (should be auto generated by amplify) with the following schema and filter

type Product @model
  @key(fields: ["id", "createdAt"]) {
  id: ID!
  name: String!
  createdAt: AWSDateTime!
  categories: [String]
}

{
  "filter": {
    "or": [
        { "categories": {"contains": "X"} },
        { "categories": {"contains": "Y"} }
    ]
  },
  "sortDirection": "DESC"
}

This will then give you products that are within category X or category Y sorted on createdAt in descending order. Hope it helps someone looking to save cost in the future during development.

Unfortunately, this won't work with more than X number of products. (X being the number in a paginated result). The filter will only filter out results on the current pagination. So you don't get back results like you would with elasticsearch.

I didn't test this yet but just by looking at the query I saw there was limit and nextToken that you can pass into the query? so I guess limit and nextToken wouldn't work with the filter condition? That is a bummer.

Edit: You are right, I guess Elasticsearch is an unavoidable cost. Thanks.

[Jmales]

@Blkc I was having this exact problem and also trying to avoid elastic search.

Did you by any chance found a way in the meantime of putting this to work without the necessity of the expensive ElasticSearch?

My example is even simpler, where I'm trying just to make a simple query for multiple values. location is an Intand ideally I would pass an array of values to filter by
myList(filter:{ animal: {contains: "cat"}, and: { or: [ {location: {eq: 2}}, {location: {eq: 10}} ]}})

[Blkc]

@Blkc I was having this exact problem and also trying to avoid elastic search.

Did you by any chance found a way in the meantime of putting this to work without the necessity of the expensive ElasticSearch?

My example is even simpler, where I'm trying just to make a simple query for multiple values. location is an Intand ideally I would pass an array of values to filter by

myList(filter:{
    animal: {contains: "cat"}, and: {
      or: [
           {location: {eq: 2}},
           {location: {eq: 10}}
    ]}})```

Your query is similar to mine and your best bet at the moment is elasticsearch if you continue down the path on DynamoDB. The point of NoSQL was meant to be sharding on keys to increase performance and searches like yours and mine are like jumping around different shards if you don't have a fix key which defeats the purpose of NoSQL. If you really don't want to pay for the cost of ES, you can try serverless aurora then you can use all of your usual SQL queries and joins.

But honestly though I have ran @searchable for a month without any upgrades and it costs me about $30. The good thing about paying for ES is that all your other models can also use @searchable without extra costs (since the indexes are built on the same machine unless you start adding more nodes for stability). And a lot of features on Amplify support DynamoDB only which also saves development time. It all comes down to trade offs which you have to make.