Filter and Limit on Amplify GraphQL Hosted by DynamoDB
Tags: graphql, aws, amplify, dynamodb
Published: 2021-08-25 16:55:24

## Background
The database is hosted by DynamoDB, and we are using Amplify GraphQL to manipulate data.
There is a table that contains information of all users of the application. Each users belong to a user group, and users should only be able to CRUD the groups' data, so I was planning on filtering table data using the `filter` parameter of the list query.
Moreover, I was trying to implement a pagination function in a list table view using nextToken. I was expecting to get constant amount of data using the `limit` parameter of the list query. 

## What Happened
   The case is, when specifying both `filter` and `limit`, DynamoDB will first get amount of data specified in `limit` and then filter those data and then throw back the filtered responses. This indicates that the response data will be less or equal to the value you specified in the `limit` parameter.

## How to handle the problem
I first modified the table definition in schema.graphql as below.

From:
``` graphql
type Post
  @model
{
  id: ID!
  userGroupId: ID!
  author: String!
  title: String!
  content: String!
  createdAt: AWSDateTime!
}

type UserGroup @model {
  id: ID!
  name: String!
  posts: [Post] @connnection (keyName: "byUserGroup", fields: ["id"]
}
```

To:
``` graphql
type Post @model
  @key ( name: "byUserGroup", fields: ["userGroupId", "createdAt"], queryField: "listPostsByUserGroups")
{
  id: ID!
  userGroupId: ID!
  author: String!
  title: String!
  content: String!
  createdAt: AWSDateTime!
}

type UserGroup @model {
  id: ID!
  name: String!
  posts: [Post] @connnection (keyName: "byUserGroup", fields: ["id"]
}
```

All I did was adding the @key section. The first parameter of `fields` will be the partition key, the second one will be the sort key.
DynamoDB will save data to separated partitions, defined by partition key. If the sort key is defined, data will be saved in each partition sorted by the sort key.

After redefining the table schema, you will be able to use `listPostsByUserGroups`, the name specified in `queryField` other than `listPosts`. You can specify the query name in `queryField`. 

```graphql
query {
  listPostsByUserGroups (userGroupId: xxxxx, sortDirection: 'DESC', limit: 10) {
    items {
      author
      title
      content
      createdAt
    }
    nextToken
  }
}
```

By using the new made `listPostsByUserGroups` query, it will constantly return amount of data you specified in the `filter` parameter until the last remaining.

## Conclusion
The behavior of DynamoDB was quite difficult as a beginner in amplify GraphQL. I still do not know how to make this happen if I had to filter with two or more columns. For instance, I was planning to have a `deletedAt` column for logical deletion and filter it as well. I concluded it impossible from my own research, yet I would like to know if anyone in this community have solutions about this.

Also, if you are looking for a way to handle pagination with GraphQL, the following post will be a great reference.

{% link https://dev.to/onlybakam/implementing-pagination-with-aws-appsync-5d08 %}