Feature Request: GraphQL query with sorting by date with secondary index

[MarlonJD]

Description

Amplify supports query with secondary index, it's required custom document, but it's really important when pagination and blank results starting to be problem. Dynamodb scans by page by page, if first page don't have all results, we have to get next page, until there won't be any nextToken. It will take time any additional costs.

ie: You got 200 comments in Comments table, you'll want specific post's comments. Your GraphQL query limit is 100. You got 3 different post ids in 200 comments randomly. If you want first 100 comments for specific post, DynamoDB won't bring you to 100 comments, you'll probably get 20 first next page 40, next page 10, next page 30. It is even possible that the first two pages will be blank.

Let's look into possible solution.

I created secondary index according to gen 1 document, you can also create secondary index in gen 2, this won't change in gen 2. So if we'll create this new ModelQuery.listByIndex() it will work on gen 1 or gen 2 at the same time.

Let's look into normal query document in GraphQL, it can be created automatically in dart with ModelQueries.list( TaskComment.classType, where: TaskComment.TASK.eq(taskId), ); :

query listTaskComments($filter: ModelTaskCommentFilterInput, $limit: Int, $nextToken: String) {
  listTaskComments(filter: $filter, limit: $limit, nextToken: $nextToken) {
    items {
      id
      content
      post {
        id
        content
        ...
      }
      taskCommentsId
      createdAt
      updatedAt
    }
    nextToken
  }

now let's looking into taskCommentsByDate query document:

query taskCommentsByDate($filter: ModelTaskCommentFilterInput, $limit: Int, $nextToken: String, @queryField: ID) {
  taskCommentsByDate(filter: $filter, limit: $limit, nextToken: $nextToken, sortDirection: DESC, taskCommentsId: $queryField) {
    items {
      id
      content
      post {
        id
        content
        ...
      }
      taskCommentsId
      createdAt
      updatedAt
    }
    nextToken
  }

This can be create modified document, secondary indexes can be found easily on models/Comment.dart:

It will looks like this:

modelSchemaDefinition.indexes = [
  amplify_core.ModelIndex(fields: const ["taskCommentsId", "createdAt"], name: "taskCommentsByDate")
];

It's our custom document to query with secondaryIndex, my proposal is using this like:

ModelQueries.listByIndex(
  TaskComment.classType,
  queryField: taskId, <--- this will be required field
  sortDirection: DESC, <--- this will be optional, default is DESC
  customQueryName: "listTasksCommentsByDate" <--- It's optional. On Gen1 user provide query name, on gen 2 it's generating automatically.
  where: TaskComment.TASK.eq(taskId),
);

Edit: I added customQueryName for gen1 and gen2 support same time.

I'll try to create this, what's your opinion about this?

Categories

• Analytics
• API (REST)
• API (GraphQL)
• Auth
• Authenticator
• DataStore
• Notifications (Push)
• Storage

Steps to Reproduce

Screenshots

No response

Platforms

• iOS
• Android
• Web
• macOS
• Windows
• Linux

Flutter Version

3.22.0

Amplify Flutter Version

1.8.0

Deployment Method

Amplify CLI

Schema

type Post @model {
  id: ID!
  content: String!
  comments: [PostComment] @hasMany
}

type Comment @model {
  id: ID!
  createdAt: AWSDateTime!
  content: String!
  taskCommentsId: ID! @index(name: "taskCommentsByDate", queryField: "taskCommentsByDate", sortKeyFields: ["createdAt"])
  post: Post! @belongsTo(fields: ["taskCommentsId"])
}

[Jordan-Nelson]

Hello @MarlonJD - Thanks for taking the time to open the issue. I want to make sure I understand the proposal. It sounds like you have been able to achieve the desired behavior with custom GraphQL queries. Your proposal is for this to be supported out of the box without the use of customer queries. Is that correct?

[MarlonJD]

Hello @Jordan-Nelson. Yes custom GraphQL is possible and it's easier with copyWith after #4365. I want to use secondary index query without custom query and variable otherwise it will waste of time. It's all there we can easily create this document automatically,

Without this proposal, it can be query with these codes:

ie:

final request = ModelQueries.list(
  Comment.classType,
  // where: TaskComment.TASK.eq(taskId), <--- same field filter shouldn't be there
);

const modifiedDoc = r"""query taskCommentsByDate($filter: ModelTaskCommentFilterInput, $limit: Int, $nextToken: String, @queryField: ID!) {
  taskCommentsByDate(filter: $filter, limit: $limit, nextToken: $nextToken, sortDirection: DESC, taskCommentsId: $queryField) {
    items {
      id
      content
      post {
        id
        content
        ...
      }
      taskCommentsId
      createdAt
      updatedAt
    }
    nextToken
  }""";

// ignore: prefer_final_locals, omit_local_variable_types
var modifiedVar = request.variables;

modifiedVar["queryField"] = taskId;

final response = await Amplify.API
        .query(
          request: request.copyWith(
            document: modifiedDocument,
            variables: modifiedVar,
            decodePath: "taskCommentsByDate",
          ),
        )
        .response;

Instead of these modifications, we can just type:

ModelQueries.listByIndex(
  TaskComment.classType,
  queryField: taskId, <--- this will be required field
  sortDirection: DESC, <--- this will be optional, default is DESC
  // where: TaskComment.TASK.eq(taskId), <-- where shouldn't be same filter, but it can be other filters
);

It will save a lot of time.

[Jordan-Nelson]

@MarlonJD Thanks for the clarification. We will track this as a feature request.

[MarlonJD]

@Jordan-Nelson I created #4945 PR, for this issue.

[MarlonJD]

@Equartey Hey there 👋. I raised PR for this proposal, I hope you can review.

[Equartey]

Hi @MarlonJD, as usual, thank you for opening the issue and the accompanying PR.

First, we will need to investigate how to best address this for all our customers before we move forward with your proposal.

This process takes time, but we will provide updates here as we have them. Thanks again.

[MarlonJD]

Hi @Equartey. Thank you very much for your reply. I'll be waiting for updates.