Add sort options to graphql schema

[palpatim]

Issue originally opened by @Crisp3333 as https://github.com/awslabs/aws-mobile-appsync-sdk-android/issues/200

---

https://github.com/awslabs/aws-mobile-appsync-sdk-android/issues/200#issue-461847003

Is your feature request related to a problem? Please describe. For cases where a query result is returned they are automatically sorted in ascending order. There should be a clear and concise way of getting a sort in descending order.I moved from direct DynamoDB querying to implementing my app in AppSync only to find out an atomic operation is hard to figure out how to implement. I have looked at other problems associated to this and only get pointed to "set scanIndexForward" without any proper way of implementing it. Where should I add this "scanIndexForward"? knowing that graphql/AppSync has encapsulated DynamoDB operations with its own functions and implementations, and without proper knowledge of writing resolvers, it leaves one playing vtl roulette with one extra headache of learning a new language. These things cut into production time when you could be focusing on actual code.

I am aslo aware of the ModelSortDirection enum type, which I believe does some form of sorting. However, my schema is basic at minimum, that is, I am not creating @connections, @functions etc. Also this enum type is not among my generated Java files.

Describe the solution you'd like Looking at the queries (vtl files (req)) generated by amplify push, the scanIndexForward field is left out, which of course will sort your query results in ascending order by default. Currently this is what the generated resolvers for model queries look like (a portion of the req.vtl file of course): #set( $ListRequest = { "version": "2017-02-28", "limit": $limit, } )

There should be away to set the scanIndexForward permanently in descending order or ascending at schema creation\modification time. I say permanently because I am assuming there must be some complications and burden for the AppSync team to let clients query DynamoDB table at will in ascending or descending order at application run time. But preferable I would like to see the sortDirection parameter become custom just like parameters nextToken, limit and filter. If the sortDirection parameter cannot be added, please at least let it be available at schema creation time to set it permanently.

A few months ago the AppSync team created the @key directive(The best thing since slide bread), which was wonderful for creating secondary index keys and sort keys. A solution that I have been thinking about is to include sortDirection in the @key directive when creating/modifying schema. For Order model, one could define:

type Order @model @key(fields: ["customerEmail", "createdAt"], sortDirection: "DSC")

Here the sortDirection parameter would be used against the sortKey field: createdAt. For people that are not using the @key directive, there could be a way sortDirection could be targeted at the primary key (Hash key).

Describe alternatives you've considered (1) It was pointed out that I could just log into the aws appsync console and include the "scanIndexForward" to be false, as it is true by default. However I plan to do that at last resort in case I break something. Evidently It seems that is what I have to do.

(2) Currently I am doing a reverse after query results returned (very inefficient) luckily I am working with a minimum amount of results.

(3) Spend a couple of weeks(time that I do not have) and learn velocity templating language and write my own resolver.

---

https://github.com/awslabs/aws-mobile-appsync-sdk-android/issues/200#issuecomment-506906907

Currently I just log into the aws appsync console choose schema and look for the resolver under query and just add "scanIndexForward": false. Doing this I just have to remember to go back and update it again after each amplify push.

[UnleashedMind]

@palpatim Thanks for your input, will put it into our backlog.

[ghost]

Hi folks, any updates on how to sort fields for a list schema?

[warrenmcquinn]

I am also interested in a simple solution for adding a default sortField to a @model -- especially one that uses the automatically-generated createdAt field.

Currently, I could add @key(fields: ["type", "createdAt"]) to enable the sortDirection input when querying, but that would require me to include createdAt in every mutation.

[aprilmintacpineda]

Also, please consider people using @searchable.

[Crisp3333]

I think I am about to write my own resolver within the next month or so as this requires me to log into the account on each amplify push and add scanIndexForward each time.

[aprilmintacpineda]

With @searchable this is how I handled the sorting:

const result = await API.graphql(
  graphqlOperation(searchTickets, {
    nextToken,
    limit,
    filter,
    sort: {
      field: createdAt,
      direction: desc
    }
  })
);

This will sort the result based on createdAt field, desc. What I haven't figured out is how to sort it by relationship, say, Tickets is associated with Status:

type Ticket @model @searchable {
  id: ID!
  description: String
  title: String
  statusId: String
  status: Status @connection(keyField: "statusId")
  createdAt: AWSDateTime
  updatedAT: AWSDateTime
}

type Status @model @searchable {
  id: ID!
  name: String
  description: String
  createdAt: AWSDateTime
  updatedAT: AWSDateTime
}

And this is only 1-1 relationship, we should also consider 1-n and n-n relationships.

[Crisp3333]

@aprilmintacpineda can the @searchable directive be used with the @model? I do not want to break my database, as appsync with graphql is very delicate.

[aprilmintacpineda]

@Crisp3333 Yes it can. AFAIK, you can only query types with @model.

[Crisp3333]

Guys I did not even realize, sortDirection is now added. It is available when you are making queries.

[aprilmintacpineda]

@Crisp3333 oh wow really!? do you have link to documentation?

[kaustavghosh06]

Yes, we do support sortDirection. We have this listed in our example #17 in this section - https://aws-amplify.github.io/docs/cli-toolchain/graphql#data-access-patterns

Let me know if this doesn't work for you.

[drclohite]

I have been looking for your example #17 in this section - https://aws-amplify.github.io/docs/cli-toolchain/graphql#data-access-patterns as a graphQL query, as I have the @key and implementing sortDirection is just not working for me. I can't see how it is done. I have set up the graphql and checked the dyanmodb table and everything looks fine, then I turn to the app code and I cannot make it work, so I remade the database and changed the field to int instead of AWSDate type to see if that made a difference.

The problem for me is listing data in index order. I start with this and get a random order: const todoData = await API.graphql(graphqlOperation(listTodos) except I want it in an order, so I try this: const todoData = await API.graphql(graphqlOperation(listTodos,{sortDirection:'DESC'})) iget an error: "When providing argument 'sortDirection' you must also provide argument 'id'.", the same as before. How do I order my data - this is a pretty basic requirement non?

[ibussieres]

I have the same issue, myself. I don't want to filter the results, just sort them.

I would expect to be able to do this : API.graphql(graphqlOperation(listTodosByName, {sortDirection:'DESC'} )).

That would be great. Am I missing something ?

[idobleicher]

@aprilmintacpineda adding searchable will much increase the cost because you will put elastic search machine upon. the easiest solution is adding a custom LSI. which the sortKey is the sort you want to make:

An example for that will be :

type PropertyComps @model(timestamps: null) 
 @key(fields: ["targetId", "compId"]) 
 @key(name: "byTargetSortedComps", fields: ["targetId", "compScore"], queryField: "byTargetSortedComps") {
  targetId: ID!
  compId: ID!
  compScore: Float
}

the byTargetSortedComps <- the sort key is the compScore. which makes it useable with the sortDirection was added to appSync.

  "data": {
    "getPropertyInfo": {
      "comps": {
        "items": [
          {
            "compScore": 3,
            "compId": "789",
            "targeId": "123"
          },
          {
            "compScore": 2,
            "compId": "456",
            "targetId": "123"
          },
          {
            "compScore": 1,
            "compId": "1234",
            "targetId": "123"
          }
        ]
      }
    }
  }
}

[ASchwad]

I agree with @drclohite and @ibussieres, this is a basic requirement to query the entities in a sorted manner.

My basic example:

type A @model{
  id: String!
  createdAt: AWSDateTime!
  description: String
}

I would like to query the last 10 entries of entity A based on the most recent createdAt timestamp. With the proposed solution, I would have to create a secondary index, which then requires again some kind of ID (like the createdAt timestamp or the entity A ID). But this is not feasible for me, as I want any entity A sorted by the most recent timestamp.

Did I miss something or is there currently no easy way to implement this feature without scans?
@kaustavghosh06 Could you please have a look at this?

Edit: For this use case, is it favorable to restructure the type in a more time-series conform way as described in the best practices?

[ASchwad]

Workaround (but not optimal) solution:

type A @model
@key(
  name: "AsByCreatedAt"
  fields: ["type" "createdAt"]
  queryField: "AsByCreatedAt"
)
{
  id: String!
  type: String
  createdAt: AWSDateTime!
  description: String
}

By adding this secondary index on type AND createdAt, the createdAt field can be efficiently used for queries. But as I only use A as type, the partioning mechanism of DynamoDB is not properly leveraged as the partion key of the secondary index will be always represented by the same value. As the data volume for my solution will be limited, this behaviour is okay-ish.

Example Use Case: Get all entries of March 2021 (limit the results to 20 entries): API.graphql(graphqlOperation(AsByCreatedAt, {type: "A", createdAt: {beginsWith: "2021-03"}, limit: 20}));

[github-actions[bot]]

This issue has been automatically locked since there hasn't been any recent activity after it was closed. Please open a new issue for related bugs.

Looking for a help forum? We recommend joining the Amplify Community Discord server *-help channels for those types of questions.