Amplify-cli: Using @key directive sorting doesn't work

[ilyador]

I want to get a certain number of image sets with the top ranking, this is my schema

type Set
  @model
  @key(name: "ByRanking", fields: ["ranking"], queryField: "byRanking") {
    id: ID!
    user: String!
    ranking: Int!
    pictures: [Picture!]! @connection
}

I get a generated query called byRanking, but it requires to specify the exact ranking, in which case I don't understand what it actually sorts. If I only get items with ranking value of X, there is nothing to sort...

query GetSetsByRanking {
  byRanking(sortDirection:ASC, ranking: 5) { # Throws an error without the ranking
    items {
      id
      ranking
    }
  }
}

If I simply do @key(fields: ["ranking"]) I get the ID field replaced by ranking and I don't have a unique ID to each set anymore. I need it to connect pictures to the set with @connection.

What am I doing wrong?
How should I first sort by ranking and then do limit: X?

This is the auto-generated query:

query ByRanking(
  $ranking: Int
  $sortDirection: ModelSortDirection
  $filter: ModelSetFilterInput
  $limit: Int
  $nextToken: String
) {
  byRanking(
    ranking: $ranking
    sortDirection: $sortDirection
    filter: $filter
    limit: $limit
    nextToken: $nextToken
  ) {
    items {
      id
      user
      ranking
      pictures {
        nextToken
      }
    }
    nextToken
  }
}

[RossWilliams]

The fields array uses the second item as the sort key, and the first item as the hash key. Try replacing fields: ["ranking"] with fields: ["id","ranking"]

[ilyador]

@RossWilliams Maybe I'm doing something wrong, but when I do what you suggested the sorting doesn't work:

Also, there are also other problems:

• I can't query sets by ID now since the key is ranking.
• When I use id as the primary key, it stops auto-generating the ID and expects me to add it in the input.
• I cannot update ranking this way since it is the sorting key now. I need to be able to update the ranking, and every time to show the highest/lowset X items.

(In my schema ranking = appearedForRanking. I was trying to avoid confusion.

[RossWilliams]

When you specify a key with a name, you are creating a secondary index. In your screenshot you are scanning the primary index, which will not be sorted. Your other bullet pointS suggest you had modified the primary index, and have not specified a name, so I’m confused on your issues.

It sounds like you want a globally sorted index. To do this you need three things

1. A single partition key for all items.
2. Your ranking number as park of a range key
3. A unique number to add to your ranking number to ensure uniqueness of the index key. CreatedAt is a good value to use

You do not want to do this on the primary index and will need to create a GSI.

[ilyador]

@RossWilliams I thought the Amplify CLI is supposed to take care of building the DB.

My goal is simple:
This is the type

type Set @model {
    id: ID!
    user: String!
    ranking: Int!
    pictures: [Picture!]! @connection
}

I need to be able to sort it by ranking, to get highest/lowest ranked sets
and to have an ID for the @connection to work.

Is it possible to achieve just by using the CLI and autogenerated code,
or do I have to manage the DB and the resolvers myself?

[RossWilliams]

You are right that the description of the goal is simple. Unfortunately your use case requires disclosing more detail on amplify transforms and dynamodb's capabilities.

The CLI and @key directive can handle your use case. Be aware dynamodb has unique properties. The database is a key/value store with sub key sorting. If you want a sorting behaviour, you must do it under a single hash key. The primary key (combined hash key and range key) must be unique, and the main index primary key is immutable. Your best option is to use the @key directive to create a GSI with a constant hash key and ranking#createdAt composite range key. Then you can update the ranking, have sorting, and maintain uniqueness. The @key documentation shows how to achieve this and how to use graphql to access the secondary index.

[ilyador]

@RossWilliams I think I forgot to mention an important detail: the ranking should be changeable.
The app meant to update rankings all the time.

Is your answer still relevant in that case?
Also, I'm not sure where in the docs does it show how to make this.
I'm guessing it is the multiple fields, but we already tried that.

[RossWilliams]

Yes, you can update the ranking when it is a range key in a secondary index. On the documeation page here search for “composite sort key”. When you say”we already tried that” can you send what exactly you tried before? Did you give your key a name so that it would be a secondary index?

The AWS documentation for GSIs includes an example use case very similar to your problem by sorting night scores in a game here

[ilyador]

@RossWilliams thanx, what I tried before and didn't work was @key(fields: ["id", "ranking"])
If I understand correctly I need to do @key(fields: ["id", "ranking", "createdAt"]), right?

[RossWilliams]

No. You need to give your key a name so that it is a secondary index. You also need to change the first field item to something that does not change, try using “__typename”. Something like @key(name: "ByRanking", fields: ["__typename”, “ranking", “createdAt”], queryField: "byRanking")

[ilyador]

I'm not sure how was I supposed to use the __typename field. I get Name "__typename" must not begin with "__", which is reserved by GraphQL introspection.

This was my attempt:

type Set
    @model
    @key(
             name: "ByRanking",
             fields: ["id", "ranking", "createdAt"],
             queryField: "ranking"
         ){
    id: ID!
    createdAt: AWSDateTime!
    user: String!
    ranking: Int!
    pictures: [Picture!]! @connection
}

However something in the DB went wrong:

Resource Name: SetTable (AWS::DynamoDB::Table)
Event Type: create
Reason: One or more parameter values were invalid: Table KeySchema does not have a range key, which is required when specifying a LocalSecondaryIndex (Service: AmazonDynamoDBv2; Status Code: 400; Error Code: ValidationException; Request ID: XXXXX)

[RossWilliams]

Apologies about suggesting __typename, this isn't possible through amplify. You need to use another constant value on your type. You could try "type", and set this value to be the same for all items.

If you change your hash key to something other than 'id', your database range key error will go away. This is due to amplify creating an LSI rather than a GSI when your hash key on the main table index is the same as your secondary index.

[ilyador]

@RossWilliams I have 2 problems:

• Since I have to use createdAt, I need to manually set it, is there a way to avoid it?
• I need to use setID for the query which is basically the opposite of what I need.

query GetByRanking{
  byRanking(sortDirection: ASC) { # fails without setId as a second parameter
    items {
      setId
    }
  }
}

Gives me Expression block '$[query]' requires an expression
This is actually the exact same result I was getting with 2 fields also.

[ilyador]

Guys, is anyone around here?
I started with Amplify thinking it is an easy way to get up and running. So far it seems I have wasted more time waiting for support that if I just have written a node server myself...

[RossWilliams]

You can post your full schema and your query you are attempting. This will help to understand the problem. It is difficult to understand how to help with partial information, such as a userId parameter that has not been mentioned, or if you have followed the previous advice.

[ilyador]

@RossWilliams I have posted my schema multiple times, like here https://github.com/aws-amplify/amplify-cli/issues/2059#issuecomment-522322084
It was my mistake here, I meant setId, which I changed from id per your suggestion here https://github.com/aws-amplify/amplify-cli/issues/2059#issuecomment-522325910

byRanking(sortDirection: ASC) { # fails without setId as a second parameter

[RossWilliams]

The first field in your schema (the partition key) needs to be the same for all items. If you are using the setId, this is incorrect, as the setId is different for each item. This is what I described in a previous comment. It is easier to help if you share your schema, because right now I don't know what you key directives look like.

[ilyador]

@RossWilliams This is the schema I pushed to AWS:

type Set
    @model
    @key(
        name: "ByRanking",
        fields: ["setId", "Ranking", "createdAt"],
        queryField: "bySetRanking"
){
    setId: ID!
    createdAt: AWSDateTime!
    user: String!
    Ranking: Int!
    pictures: [Picture!]! @connection
}

[RossWilliams]

@ilyador Do you understand the updates to make to your schema?

[attilah]

@ilyador with the following schema you can achieve what you are looking for:

type Set
@model
@key(name: "ByRanking", fields: [ "type", "ranking" ], queryField: "getSetsByRanking")
{
    id: ID!
    type: String!
    user: String!
    ranking: Int!
}

I've excluded the Pictures, since it is indifferent for the problem you have.

This schema will create a GSI named ByRanking and enables you to query AND even filter on ranking if needed and of course you can sort.

Notice the type field, that acts like the HASH key for the GSI, you must set it to 'Set' always or you can update the resolvers to get it handled for you.

After you create some data with a mutation like this:

mutation {
  createSet(input:
  {
    id: "1"
    type: "Set"
    user: "User1"
    ranking: 5
  })
  {
    id
  }
}

This query will return you all the items in descending order by ranking and with paging support:

query GetByRanking {
  getSetsByRanking(type: "Set", sortDirection: DESC)
  {
    items
    {
      id
      type
      user
      ranking
    }
  }
}

If you just need the data which has a lower rating than 5 you can add filtering to it:

query GetByRanking {
  getSetsByRanking(type: "Set", ranking: { lt: 5 }, sortDirection: DESC)
  {
    items
    {
      id
      type
      user
      ranking
    }
  }
}

I hope this sample solves your problem.

[ilyador]

@attilah yes! The type was the missing piece.