AppSync - Sorting by fields

[lusty kelp]

You could use sort keys to achieve what you want. For example you could add to Message:
chatId: ID! @index(name: "messageByChatByUpdatedAt", sortKeyFields: ["updatedAt"])
Now you could query all messages from a day range (between two dates, or lets say all messages from the start of 2023), and they are returned to you sorted by updatedAt. One thing to note though is that at maximum 1mb of data will be returned with one Query. Search for "short key" in the docs: https://docs.amplify.aws/cli/graphql/data-modeling/#configure-a-secondary-index

@searchable in general is the most flexible option for data search and aggregation , but it can be quite expensive*. And the t2.small instance tend to crash from time to time , so you should not use that in production.

• https://aws.amazon.com/opensearch-service/pricing/

I suppose you could add sort key to your messageBySender index, which is used by the hasMany relation

I think this is a good example of how composite sort key. (byCustomerByStatusByDate) https://docs.amplify.aws/cli/graphql/examples-and-solutions/#6-show-all-open-orders-within-a-given-date-range-across-all-customers

I could be wrong too, but I don't think using composite sort key with two dates would work the way you wanted to here.

You want to get these right? These do require two different queries

1. All messages from a chat sorted by updatedAt
2. All messages from a chat sorted by createdAt

I believe so yes, at least I don't see why that would not work 😄

So for example you would have messagesByUpdatedAt and messagesByCreatedAt on User, both having their of index with a single sort key? On your GraphQL query you can choose what you want returned in the response, both, just one of them or neither.

Ah, again in my opinion you would want to specify two different indexes with single sort keys, instead of having that composite sort key. You can of course try if it works for you, but I'm guessing it wont do what you want to

Btw just curious, what is the use case that requires you to fetch messages both by createdAt and updatedAt?

[minor cove]

A DynamoDb table can have maximum of 5 indexes. If you want more ‘searchable’ functionality, consider to use @searchable directive which creates an OpenSearch cluster and the search will perform on it. It brings additional costs on the table.

[lusty kelp]

Amplify @index directive creates Global secondary indexes, where the default quota is 20. And is seems to be possible to raise the limit through support

@rigid hatch Oh right, that might be one of the downsides of this approach.

DynamoDB is really different from relational databases, and might not always be the right choice. However it is possible to design the data access patterns in a way that most of the apps can be sensible using it.