Amplify-js: DataStore: How to do a sort-query

[pubkey]

* Which Category is your question related to? *
Storage
* What AWS Services are you utilizing? *
DataStore, AppSync
* Provide additional details e.g. code snippets *

When I do a query with DataStore.query(), how can do sorting?
The use-case is to get the user document with the highest number X in an attribute. The docs only describe simple queries.

[elorzafe]

@pubkey thanks for your feedback, I will mark this as a feature-request

[pubkey]

@elorzafe thank you.

[lenarmazitov]

This feature is really necessary.
Simple chat application can not be created with DataStore, because I need sort messages by descending date, but it can not be done with current version.
Is it already on roadmap?

[undefobj]

@pubkey @lenarmazitov can you share your schemas and queries? Are you using @key as defined here: https://aws-amplify.github.io/docs/js/datastore#relational-models

[lenarmazitov]

@undefobj I mean there is no way to make directions for Delta Sync with any sorting attribute and order. This is actual for @searchable models (with Elastic Search)

[undefobj]

@lenarmazitov DataStore does not work with @searchable as those are Full-Text Search queries powered by Amazon Elasticsearch in the cloud. Instead DataStore functions by synchronizing @model objects to your device using DynamoDB and optimizes this with a change journal which we call Delta Sync. Since DynamoDB supports capabilities such as index based sorting and filters which can be applied to the result sets we're looking to integrate this a bit further into DataStore. To do this work it would be very helpful in our design if we could get your and @pubkey 's data model (e.g. GraphQL schema) as well as the results you expect to get back when running a DataStore.query() so that we can ensure we're meeting your use case.

[pubkey]

I do not think that you need our data models. Just use the ones in the DataStore tutorial.

[rpostulart]

I also have this need, now I still use seperate appsync calls insteand of datastore queries

[Yukimune]

Also I need "ScanIndexForward" option on DataStore.

[elchicofrommo]

So is sorted results from DataStore still not working? Here's an example of a super simple model and query I'm using and I'm not getting the results in sorted order
Schema:
type Gratitude @model @auth(rules: [{allow: owner}])
@key( fields:["email", "time"])
{
email: String!
title: String!
time: Int!
entries: [String]!
}

Query:
DataStore.query(GratitudeModel, c=>{c.email("eq", email).time("lt", new Date().getTime())} )

[IvanCaceres]

We still need sorting for Datastore queries, simple Ascending / Descending order for strings and integers would work great, like traditional SQL orderBy databases.

[IvanCaceres]

In addition to adding sorting for fields on a DataStore record (Ascending/Descending) for strings and integers, I think this would go in hand with offering full pagination functionality, where a length(amount of records) or total pages is returned in the data response so frontend applications can integrate that into a Paginator.

[swaminator]

@IvanCaceres for pagination check out these docs: https://docs.amplify.aws/lib/datastore/data-access/q/platform/js#pagination

[IvanCaceres]

@swaminator I've worked with that documentation on Pagination however it's insufficient for front-end applications implementing Paginators (where you'd like to have a list of buttons/dropdown for navigation to all pages or a specific page). This is because the Datastore paginated data response doesn't send back length information on the quantity of pages / length of total records.

This compounds with the fact there's no sorting or ordering of Datastore records, it greatly decreases the utility of the application by preventing users from specifying which items they'd like to see first as well as preventing a paginated UI solution for users going to the last page or a specific page in general to pick up where they left off with their latest Datastore records.

[undefobj]

@IvanCaceres sorting is under active development at this time. We have a finalized design and are working through implementation in the coming weeks.

[IvanCaceres]

Thank you guys for your work, you're champs 🏆

[iartemiev]

Query sorting was added in #6785 and has been released as part of [email protected] / @aws-amplify/[email protected]

Please feel free to start using it.

Here's a link to the docs for sort.

[chai86]

@iartemiev How would you call this using GraphQL to sort? e.g.

try {
    const userData = await API.graphql(graphqlOperation(GetUsers, { id: "8578735-dnd83-33944dda-asd44-943nk8n3h" }));
}  

[iartemiev]

@chai86 if you're looking for clientside sorting on arbitrary fields, that is only supported out-of-the-box in DataStore. In order to replicate it using the API category, you would need to retrieve all of the items in your table and then manually sort them in your app, e.g., using Array.sort.

For serverside sorting, you can leverage the @key directive to create an LSI or GSIs with sort fields and then pass a sortDirection param in your query.

[chai86]

@iartemiev I definitely don't want to pull the whole table to the users mobile phone and then sort!

I have a DynamoDB table called URLArray that contains a list of URL's (myURL) and a unique video number (myNum).

I use AWS Amplify to query my table like so for example:

URLData = await API.graphql(graphqlOperation(getUrlArray, { id: "173883db-9ff1-4...."}));

Also myNum is a GSI, so i can also query the row using it, for example:

`URLData = await API.graphql(graphqlOperation(getURLinfofromMyNum, { myNum: 5 }));`

The queries.js has this inside:

export const getURLinfofromMyNum = `query GetURLinfofromMyNum(
  $vidNumber: Int
  $filter: ModelURLArrayFilterInput
  $limit: Int
  $nextToken: String
) {
 getURLinfofromMyNum(myNum: $myNum, filter: $filter, limit: $limit, nextToken: $nextToken) {
    items {
      id
      myDate
      myURL
      myNum!
    }
    nextToken
  }
}
`;

My question is, I would like to simply query this table to know what the maximum number of myNum is. So in this picture it'd return myNum = 12. How do i query (or what command do i use) my table using sortDirection to get this maximum number?

[IvanCaceres]

@iartemiev I'm attempting to do a simple ascending sort on a string field with Datastore but not seeing the model results being sorted:

const inspectionsData = await DataStore.query(Inspection, Predicates.ALL, {
   sort: s => s.loanId('ASCENDING')
});

And this is what that bit of schema looks like:

type Inspection @model
  @key(name: "byLender", fields: ["lenderId", "id"])
  @auth(rules: [
    { allow: private, provider: userPools },
    { allow: private, provider: iam },
    { allow: public, provider: apiKey }
  ]) {
  id: ID!
  name: String!
  streetAddress: String!
  city: String!
  zip: String
  lot: String
  phases: Int!
  inspectionValues: [InspectionValue!]!
  lenderId: ID!
  workDescriptions: [WorkDescription!]!
  currentInspectionPhase: Int!
  loanId: String!
  images: [PhaseImages!]
  searchField: String
  borrowerSameAsLender: Boolean
  borrower: String
  subdivision: String
  lender: Lender @connection(fields: ["lenderId"])
  inspectionsInfo: [InspectionInfo]
  comments: String
  archived: Boolean
  drafts: [Draft] @connection(keyName: "byInspection", fields: ["id"])
}

I'd greatly appreciate some insight as to why the sorting isn't working when following the example in the docs.