Filtering List Query By Another Table's Field (a.k.a Cross-Table or Nested Filtering) in AWS Amplify GraphQL DynamoDB

Question

Which Category is your question related to? DynamoDB, AppSync(GraphQL)

Amplify CLI Version 4.50.2

Provide additional details e.g. code snippets

BACKGROUND: I'm new in AWS serverless app systems and as a frontend dev, I'm quite enjoying it thanks to auto-generated APIs, tables, connections, resolvers etc. I'm using Angular/Ionic in frontend and S3, DynamoDB, AppSync, Cognito, Amplify-cli for the backend.

WHAT I HAVE: Here is a part of my schema. I can easily use auto-generated APIs to List/Get Feedbacks with additional filters (i.e. score: { ge: 3 }). And thanks to the @connection I can see the User's details in the listed Feedback items.

type User @model @auth(rules: [{ allow: owner }]) {
  id: ID!
  email: String!
  name: String!
  region: String!
  sector: String!
  companyType: String!
}

type Feedback @model @auth(rules: [{ allow: owner }]) {
  id: ID!
  user: User @connection
  score: Int!
  content: String
}

WHAT I WANT: I want to list Feedbacks based on several fields on User type, such as user's region (i.e. user.region: { contains: 'United States' }). Now I searched for a solution quite a lot like, #2311 , and I learned that amplify codegen only creates top-level filtering. In order to use cross-table filtering, I believe I need to modify resolvers, lambda functions, queries and inputs. Which, for a beginner, it looks quite complex.

WHAT I TRIED/CONSIDERED:

1. I tried listing all Users and Feedbacks separately and filtering them in front-end. But then the client downloads all these unnecessary data. Also because of the pagination limit, user experience takes a hit as they see an empty list and repeatedly need to click Load More button.
2. Thanks to some suggestions, I also thought about duplicating the User details in Feedback table to be able to search/filter them. Then the problem is that if User updates his/her info, duplicated values will be out-of-date. Also there will be too many duplicated data, as I need this feature for other tables also.
3. I also heard about using ElasticSearch for this problem but someone mentioned for a simple filtering he got 30$ monthly cost, so I got cold feet.
4. I tried the resolver solution to add a custom filtering in it. But I found that quite complex for a beginner. Also I will need this cross-table filtering in many other tables as well, so I think would be hard to manage. If that is the best-practice, I'd appreciate it if someone can guide me through it.

QUESTIONS:

1. What would be the easiest/beginner-friendly solution for me to achieve this cross-table filtering? I am open to alternative solutions.
2. Is this cross-table filtering a bad approach for a no-SQL setup? Since I need some relationship between two tables. (I thought @connection would be enough). Should I switch to an SQL setup before it is too late?
3. Is it possible for Amplify to auto-generate a solution for this in the future? I feel like many people are experiencing the same issue.

Thank you in advance.

Answer 1

My former answer can still be useful for others in specific scenarios, but I found a better way to achieve nested filtering when I realized you can filter nested items in custom queries.

Schema:

    type User @model {
        id: ID!
        email: String!
        name: String!
        region: String!
        sector: String!
        companyType: String!
        feedbacks: [Feedback] @connection # <-- User has many feedbacks
    }

Custom query:

    query ListUserWithFeedback(
        $filter: ModelUserFilterInput # <-- Filter Users by Region or any other User field
        $limit: Int
        $nextToken: String
        $filterFeedback: ModelFeedbackFilterInput # <-- Filter inner Feedbacks by Feedback fields
        $nextTokenFeedback: String
        ) {
        listUsers(filter: $filter, limit: $limit, nextToken: $nextToken) {
            items {
            id
            email
            name
            region
            sector
            companyType
            feedbacks(filter: $filterFeedback, nextToken: $nextTokenFeedback) {
                items {
                content
                createdAt
                id
                score
                }
                nextToken
            }
            createdAt
            updatedAt
            }
            nextToken
        }
    }

$filter can be something like:

    { region: { contains: 'Turkey' } }

$filterFeedback can be like:

    {
        and: [{ content: { contains: 'hello' }, score: { ge: 4 } }]
    }

This way both Users and Feedbacks can be filtered at the same time.