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

Amplify, and really DynamoDB in general, requires you to think about your access patterns ahead of time. There is a lot of really good information out there to help guide you through what this thought process can look like. Particularly, I like Nader Dabit's https://dev.to/dabit3/data-modeling-in-depth-with-graphql-aws-amplify-17-data-access-patterns-4meh

At first glance, I think I would add a new @key called byCountry to the User model, which will create a new Global Secondary Index on that property for you in DDB and will give you some new query methods as well. Check out https://docs.amplify.aws/cli/graphql-transformer/key#designing-data-models-using-key for more examples.

Once you have User.getByCountry in place, you should then be able to also bring back each user's Feedbacks.

query USAUsersWithFeedbacks {
  listUsersByCountry(country: "USA") {
    items {
      feedbacks {
        items {
          content
        }
        nextToken
      }
    }
    nextToken
  }
}

Finally, you can use JavaScript to fetch all while the nextToken is not null. You will be able to re-use this function for each country you are interested in and you should be able to extend this example for other properties by adding additional @keys.

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.

Ok thanks to @alex's answers I implemented the following. The idea is instead of listing Feedbacks and trying to filter them by User fields, we list Users and collect their Feedbacks from the response:

1. Updated schema.graphql as follows:

    type User
        @model
        @auth(rules: [{ allow: owner }])
        @key(name: "byRegion", fields: ["region"], queryField: "userByRegion") # <-- added byRegion key {
        id: ID!
        email: String!
        name: String!
        region: String!
        sector: String!
        companyType: String!
        feedbacks: [Feedback] @connection # <-- added feedbacks connection
    }
   

2. Added userFeedbacksId parameter while calling CreateFeedback. So they will appear while listing Users.

3. Added custom query UserByRegionWithFeedback under src/graphql/custom-queries.graphl and used amplify codegen to build it:

    query UserByRegionWithFeedback(
        $region: String
        $sortDirection: ModelSortDirection
        $filter: ModelUserFilterInput
        $limit: Int
        $nextToken: String # <-- nextToken for getting more Users
        $nextTokenFeedback: String # <-- nextToken for getting more Feedbacks
       ) {
        userByRegion(
            region: $region
            sortDirection: $sortDirection
            filter: $filter
            limit: $limit
            nextToken: $nextToken
        ) {
            items {
            id
            email
            name
            region
            sector
            companyType
            feedbacks(nextToken: $nextTokenFeedback) { 
                items {
                content
                createdAt
                id
                score
                }
                nextToken
            }
            createdAt
            updatedAt
            owner
            }
            nextToken
            }
         }
   

4. Now I call this API like the following:

    nextToken = {
        user: null,
        feedback: null
    };
    feedbacks: any;
   
    async listFeedbacks() {
        try {
            const res = await this.api.UserByRegionWithFeedback(
                'Turkey', // <-- region: filter Users by their region, I will add UI input later
                null, // <-- sortDirection
                null, // <-- filter
                null, // <-- limit
                this.nextToken.feedback == null ? this.nextToken.user : null, // <-- User nextToken: Only send if Feedback NextToken is null
                this.nextToken.feedback // <-- Feedback nextToken
            );
   
            // Get User NextToken
            this.nextToken.user = res.nextToken;
            // Initialize Feedback NextToken as null
            this.nextToken.feedback = null;
            // Loop Users in the response
            res.items.map((user) => {
            // Get Feedback NextToken from User if it is not null (Or else last User in the list could overrite it)
            if (user.feedbacks.nextToken) {
                this.nextToken.feedback = user.feedbacks.nextToken;
            }
            // Push the feedback items into the list to diplay in UI
            this.feedbacks.push(...user.feedbacks.items);
            });
        } catch (error) {
            this.handleError.show(error);
        }
    }
   

5. Lastly I added a Load More button in the UI which calls listFeedbacks() function. So if there is any Feedback NextToken, I send it to the API. (Note that multiple user feedbacks can have a nextToken). If all feedbacks are ok and if there is a User NextToken, I send that to the API and repeat the process for new Users.

I believe this could be much simpler with an SQL setup, but this will work for now. I hope it helps others in my situation. And if there is any ideas to make this better I'm all ears.