Amplify-cli: Unable to retrieve list of "ordered" items using @key and secondary index using "sortDirection"

[matt-e-king]

Describe the bug
When using the @key decorator for my primary index, I can't get sortDirection to return a list of ordered items. The payload is always the same regardless of sortDirection: ASC|DESC. When I create a secondary index using the @key decorator, I receive a MappingTemplate error.

I could be overlooking something simple, but all I want to accomplish it to retrieve a list of items ordered by a different attribute other than createdAt and I can't get this to work.

To Reproduce
Steps to reproduce the behavior:

1. Create a GraphQL type in schema that looks like this:

type Twig
    @model
    @key(fields: ["id", "date"])
    @auth(rules: [
        { allow: owner }
    ])
{
    id: ID!
    title: String!
    content: String
    date: AWSDateTime!
    photos: [String]
}

This builds out the API no problem. From what I've read, you can't re-order on the primary index in DynamoDB regardless, but please keep reading as I am just documenting my steps.

1. After adding some items to the table, perform a query that looks like this through the console:

query ListTwigs(
  $id: ID
  $date: ModelStringKeyConditionInput
  $filter: ModelTwigFilterInput
  $limit: Int
  $nextToken: String
  $sortDirection: ModelSortDirection
) {
  listTwigs(
    id: $id
    date: $date
    filter: $filter
    limit: $limit
    nextToken: $nextToken
    sortDirection: $sortDirection
  ) {
    items {
      id
      title
      content
      date
      photos
      owner
    }
    nextToken
  }
}

// Query Variables
{
  "sortDirection": "DESC"
}

Or in the code:

graphqlOperation(graphqlQueries.listTwigs, {
  limit: 100,
  sortDirection: 'DESC'
})

The payload never changes regardless of the sortDirection variable. As mentioned above, I read that you can't order items on the primary index, so I attempted to create a secondary index using the @key decorator.

1. Create secondary index - remove the existing API amplify remove api and amplify push and then add a new API with the following schema:

type Twig
    @model
    @key(fields: ["id", "createdAt"])
    @key(name: "OrderedTwigs", fields: ["id", "date"], queryField: "orderedTwigs")
    @auth(rules: [
        { allow: owner }
    ])
{
    id: ID!
    title: String!
    content: String
    createdAt: String!
    date: AWSDateTime!
    photos: [String]
}

Run amplify push and this will build just fine.

1. After adding some items to the table, perform a query like this through the console:

query OrderedTwigs(
  $id: ID
  $date: ModelStringKeyConditionInput
  $sortDirection: ModelSortDirection
  $filter: ModelTwigFilterInput
  $limit: Int
  $nextToken: String
) {
  orderedTwigs(
    id: $id
    date: $date
    sortDirection: $sortDirection
    filter: $filter
    limit: $limit
    nextToken: $nextToken
  ) {
    items {
      id
      title
      content
      createdAt
      date
      photos
      owner
    }
    nextToken
  }
}

// Query Variables
{
  "sortDirection": "DESC"
}

Or through code using the new orderedTwigs codegen query:

graphqlOperation(graphqlQueries.orderedTwigs, {
  limit: 100,
  nextToken: token,
  sortDirection: 'DESC'
})

When using the secondary index with a queryField named orderedTwigs I start receiving MappingTemplate errors that look like this:

{
  "data": {
    "orderedTwigs": null
  },
  "errors": [
    {
      "path": [
        "orderedTwigs"
      ],
      "data": null,
      "errorType": "MappingTemplate",
      "errorInfo": null,
      "locations": [
        {
          "line": 9,
          "column": 3,
          "sourceName": null
        }
      ],
      "message": "Expression block '$[query]' requires an expression"
    }
  ]
}

Expected behavior
To get a list of ordered items using the @key decorator. I might be overlooking something simple to accomplish this, but I'm sure the scenario above still shouldn't be producing a MappingTemplate error.

Ultimately, I just want to get a list of ordered items using a different attribute besides createdAt. Any advice? Thanks!

Desktop (please complete the following information):

• OS: macOS Sierra 10.13.6
• Browser Chrome
• Version 77.0.3865.120

Amplify CLI

• Version 3.15.0

[jkeys-ecg-nmsu]

@matt-e-king Have you tried logging graphqlQueries.orderedTwigs to make sure the query is being generated correctly?

[matt-e-king]

@jkeys-ecg-nmsu the code sample I provide above on #5 is the code generated by the CLI:

query OrderedTwigs(
  $id: ID
  $date: ModelStringKeyConditionInput
  $sortDirection: ModelSortDirection
  $filter: ModelTwigFilterInput
  $limit: Int
  $nextToken: String
) {
  orderedTwigs(
    id: $id
    date: $date
    sortDirection: $sortDirection
    filter: $filter
    limit: $limit
    nextToken: $nextToken
  ) {
    items {
      id
      title
      content
      createdAt
      date
      photos
      owner
    }
    nextToken
  }
}

I copied that directly out of src/graphql/queries.js which is the location the CLI is configured to put code-generated files.

Is that what you are referring to?

[matt-e-king]

@attilah Any advice? I'm just looking for guidance on how to scaffold out queries that return items sorted/ordered using Amplify.

[matt-e-king]

Turns out this was a combination of my lack of understanding, and in my opinion, misleading (incomplete) documentation about querying a secondary index.

TL;DR; - you can't use scanIndexForward without a query expression. Scroll to bottom to read more on this.

In my example above:

@key(fields: ["id", "createdAt"])
@key(name: "OrderedTwigs", fields: ["id", "date"], queryField: "orderedTwigs")

This code generates two queries and are essentially identical, but with two different names listTwigs and orderedTwigs. However, the velocity templates that are created in amplify/backend/twigsApi/build/resolvers/Query.*.req.vtl are a little different.

The main difference is that in the velocity template that is created for orderedTwigs attempts to create a Dynamo Query like this:

{
  "version": "2017-02-28",
  "operation": "Query",
  "limit": $limit,
  "query": $modelQueryExpression,
  "index": "OrderedTwigs"
}

The operation and query properties are hard coded respectively to Query and $modelQueryExpression. The value $modelQueryExpression is conditionally created earlier in the vtl template. The problem being, you MUST past values (an expression) for your primary key and range in order for the $modelQueryExpression to be set (see below). Otherwise, it is conditionally left as an empty {}. Which throw this MappingTemplate error.

This means that if you create a secondary index like this:

@key(name: "OrderedTwigs", fields: ["id", "date"], queryField: "orderedTwigs")

You must generate a graphqlOperation like this:

API.graphql(
  graphqlOperation(graphqlQueries.orderedTwigs, {
    limit: 100,
    nextToken: token,
    id: '5ed45fcf-bea6-4bf3-8043-52fd0b68519c',
    date: {
       lt: '2020-11-03T08:16:32.3232-07:00'
    },
    sortDirection: 'DESC' // TODO: doesn't fit the generic setup
  })
)

id and date (or whatever fields you used in your @key decorator) must have an associated expression or you will receive the MappingTemplate - Expression block '$[query]' requires an expression error.

The velocity template for the primary index @key(fields: ["id", "createdAt"]) handles this differently. It does not hard code the operation and query properties, it first checks if there is an expressions and THEN dynamically sets the operation: Query (rather than Scan).

The reason for all this, is that the vtl template for the secondary index is assuming you will always have a query expression (and sortDirection which sets the property of scanIndexFoward: true|false), because both query and scanIndexFoward are un-supported properties if you are running a Scan.

TL;DR;
The heart of all of this, is better understanding the difference between a Query and a Scan .

The sortDirection that you set in your graphqlOperation directly dictates the scanIndexForward value - but the scanIndexForward property is only supported on a operation: query, which REQUIRES an expression.

Requiring a query expression seems to complete influence how you design your attributes if you want simple ordering (especially in a simple app where you don't use your primary key and range combination to filter by 'owner). I'll report back with my findings.

[jkeys-ecg-nmsu]

@matt-e-king I think this is a bug in the generated resolver, you should be able to query an index without specifying all the sort keys. That's the whole reason for having a sort key! You can verify this by going to the Dynamo console and query on your secondary index using just the partition key. The resolver should build the query expression based on the subset of values passed in, so you can pass in just id and still query by direction. @kaustavghosh06

[matt-e-king]

I agree Jeremy, there is definitely a bug in the way the velocity template is generated when you use a @key decorator like this:

@key(name: "OrderedTwigs", fields: ["id", "date"], queryField: "orderedTwigs")

But once I figured that out, it also made me realized some of the mistakes I was making in my table schema in order to get the type of sorting I wanted.

So yes, there should be a bug report on the velocity template generated, but I no longer believe there is a sorting/ordering issue.

[malcomm]

Any updates on this? I'm running into the same issue and am unable to perform a search using @key and secondary sort field.

[matt-e-king]

@malcomm this ended up being a weird, unclear, convoluted issue. I can actual sort just fine (which I'll explain shortly).

The real bug is in the velocity template that amplify-cli is generating when you have a secondary index, like this:

@key(name: "OrderedTwigs", fields: ["family", "date"], queryField: "orderedTwigs")

The velocity template that is auto-generated assumes that if you are going to use the GraphQL query generated by by the above secondary index, that you will ALWAYS pass in additional query expressions. Like this:

API.graphql(
        graphqlOperation(orderedTwigs, {
          family: 'KING',
          date: {
                lt: dayjs().format(AWS_DATETIME_FORMAT)
          },
          sortDirection: 'DESC',
          limit: 100
})

Basically, the velocity template removes any logic to determine if its a Scan or a Query - it just always assumes it's a Query which requires an expression. This is why I was getting MappingTemplate errors which I documented in my original post.

That all said, getting sorting to work, for me, required a better understanding of how AppSyncs resolvers work - which also forced me to rethink my DynamoDB structure.

sortDirection maps directly to scanIndexForward (again read here). But scanIndexForward is only a valid property on a Query, and a Query requires a query expression

(and if you look closely at how the if/else logic in the velocity template is written, amplify/backend/twigsApi/build/resolvers/Query.*.req.vtl, you have to pass at least the primary key that you declared in your secondary index in order for it to be a "valid query expression")

So you CAN'T sort like this:

API.graphql(
        graphqlOperation(orderedTwigs, {
          sortDirection: 'DESC',
          limit: 100
})

But you you CAN sort like this:

API.graphql(
        graphqlOperation(orderedTwigs, {
          family: 'KING',
          date: {
                lt: dayjs().format(AWS_DATETIME_FORMAT)
          },
          sortDirection: 'DESC',
          limit: 100
})

(family is my primary key in the above example)

The whole point of creating a secondary index, it to partition "like" data. I had a REALLY simple database for my app, where ALL users will get the same data, so I wasn't thinking in terms of using a secondary index to partitions my data. In my database, I had to create this extraneous family attribute in order to get this to work.

Sorry, long winded, I hope this helps.

[attilah]

The results you're seeing is the behavior of DynamoDB, which is different from the SQL databases. I'd suggest to read about how to design (redesign) data for DynamoDB by starting out from access patterns and such. At re:Invent there were some sessions around it.

https://www.youtube.com/watch?v=DIQVJqiSUkE
https://www.youtube.com/watch?v=6yqfmXiZTlM

As the problem is resolved I'm closing this issue.