@key on model search for multiple values for the fields

[malcomm]

Which Category is your question related to?
graphql / DynamoDB

Amplify CLI Version

4.13.1

What AWS Services are you utilizing?

AppSync / DynamoDB

Provide additional details e.g. code snippets

type Foo
  @model

  @key(name: "FooStatusUpdatedAt", fields: ["status", "updatedAt"], queryField: "fooStatusUpdatedAt")
{
  id: ID!,
  updatedAt: AWSDateTime,
  status: PublicationStatus,
....

This will generate something like this:

  async FooStatusUpdatedAt(
    status?: PublicationStatus,
    updatedAt?: ModelStringKeyConditionInput,
    sortDirection?: ModelSortDirection,
    filter?: ModelFooFilterInput,
    limit?: number,
    nextToken?: string
  ): Promise<FooStatusUpdatedAtQuery> {
    const statement = `query FooStatusUpdatedAt($status: PublicationStatus, $updatedAt: ModelStringKeyConditionInput, $sortDirection: ModelSortDirection, $filter: ModelFooFilterInput, $limit: Int, $nextToken: String) {
        fooStatusUpdatedAt(status: $status, updatedAt: $updatedAt, sortDirection: $sortDirection, filter: $filter, limit: $limit, nextToken: $nextToken) {

This is great, but what if I want to search for multiple PublicationStatus values? Right now I only see a way to search for a single status. Is there another way of doing this without? (w/o multiple queries)

[dantasfiles]

The three ways I know of are to 1) do multiple fooStatusUpdatedAt queries, 2) do a DynamoDB Scan with the listFoo operation on an and filter (which is reall non-optimal), or 3) use a batch operation (Link 1 - Link 2) Which one you choose is determined by your particular needs.

[malcomm]

@dantasfiles - thank you for your response.

1) Multiple Queries. I'm really trying to avoid this, because I have a table that will have a lot of items in it and pagination. I have 8 different statuses here in play and so just managing all that gets a little complicated quickly. Not to mention really inefficient.

2) Scan/List. I'm currently doing this but the data coming back is not sorted by what I want ("updatedAt"). This is one of the reasons why I have a named @key with a sort field on it.

3) Batch operations. Batch operations just seem so painful. I wasn't aware of them until now, so thank you. I scanned the links you sent and I don't see a great pathway to write a simple get for these, I will continue to look into them, but this seems less than ideal.

But that still has me scratching my head on this one ... why not just support multiple here? (via the normal and, or, eq ... etc.)

[dantasfiles]

2. The reason your scans are not coming back sorted is because your default primary index, used by the listFoo operation, is id. You have to override that with an additional directive like @key(fields: ["id", "updatedAt"]).
   Scans touch every element of a DynamoDB table (the filter runs on the result of fetching the whole table), so they can be very expensive.

There's another way--I remembered a post I read:
https://dev.to/open-graphql/graphql-recipes-building-apis-with-graphql-transform-3jp0#events-app

type Foo
  @model
  @key(name: "FooStatusUpdatedAt", fields: ["queryName","status"], queryField: "fooStatusUpdatedAt")
{
  id: ID!,
  updatedAt: AWSDateTime,
  status: PublicationStatus,
  queryName: String!

The queryName of every item is always "Foo"

The gives you
fooStatusUpdatedAt(queryName: String, status: ModelStringKeyConditionInput...): ModelFooConnection
where ModelStringKeyConditionInput is

input ModelStringKeyConditionInput {
  eq: String
  le: String
  lt: String
  ge: String
  gt: String
  between: [String]
  beginsWith: String
}

It's not what you want, but it's more efficient than a scan if you can figure something clever out with the between or beginsWith conditions

[malcomm]

Yeah during my reply to you I was investigating the primary index. I got stopped by two things:

type Foo
  @model
  @key(fields: ["id", "updatedAt"])
  @key(name: "FooStatusUpdatedAt", fields: ["status", "updatedAt"], queryField: "fooStatusUpdatedAt")
{
  id: ID!,
  updatedAt: AWSDateTime,
  status: PublicationStatus,
....

Resulted in this:

The primary @key on type 'StudyEncounter' must reference non-null fields.

So I've run into this before ... and I have to say createdAt and updatedAt should be automatically available and we shouldn't have to declare them in the schema. To get around this, I have to do:

  updatedAt: AWSDateTime!,

So now, I'm required to supply an updatedAt from the client .... which is just wrong.

Even with that ... I changed the primary index and got this error:

UPDATE_FAILED SubscriptiononUpdateFooResolver AWS::AppSync::Resolver Tue Feb 04 2020 12:55:28 GMT-0800 (Pacific Standard Time) Resource update cancelled                                                                                                                                                 
UPDATE_FAILED FooTable                        AWS::DynamoDB::Table   Tue Feb 04 2020 12:55:28 GMT-0800 (Pacific Standard Time) CloudFormation cannot update a stack when a custom-named resource requires replacing. Rename Foo-wgujhut...2zme-foodev and update the stack again.

So I'm guessing I have to manually delete the index and then allow this update to happen to re-create. This is really frustrating from an app dev perspective.

---- Edit ----
So no ... it looks like amply-cli wants me to rename the table???

I'm stuck now ....

I can't get amply push to work. I've manually removed all indexes and then tried the amply push. I've tried re-creating the indexes ... same error. I mean I know that indexing can be expensive but come on.

Any help?

[malcomm]

@mikeparisstuff - I wonder if you might be able to weigh in on this. Basically at this point I have a model looking like this:

type Foo
  @model
  @key(name: "FooStatusUpdatedAt", fields: ["status", "updatedAt"], queryField: "fooStatusUpdatedAt")
{
  id: ID!,
  updatedAt: AWSDateTime,
  status: PublicationStatus,

And then I try and change the primary index to:

type Foo
  @model
  @key(fields: ["id", "updatedAt"])
  @key(name: "FooStatusUpdatedAt", fields: ["status", "updatedAt"], queryField: "fooStatusUpdatedAt")
{
  id: ID!,
  updatedAt: AWSDateTime!,
  status: PublicationStatus,

And I can't do an amplify push. This is even after I manually delete the primary index. IMHO - amplify-cli should be smart enough to see that the primary index is no longer there and re-create no?

Also if you might be able to provide any insight into the fact that my original problem (see description).

Thank you!

---- Edit -----

So this is just beyond frustrating .... I'm trying to resolve this by removing the table and replacing it via these instructions: Resolve CloudFormation and I'm in a quagmire of editor hell. It makes me really start thinking: "how is amplify-cli with DynamoDB even close to be ready for prime time" .... All I'm trying to do is add a default sort to the primary index and the stack just fails miserably. I can see a justification for not supporting this w/o manual intervention. But if I remove the index in question, can someone please explain to me how an amplify push would fail after that?

---- Edit #2 ----
So I finally got my amplify push to work. I had to remove the model from my schema and all references to it and I performed an amplify push. I then re-added and did another amplify push which failed because I was getting errors similar to: Issue #682. I had to do the manual step of going to AppSync and creating a new resource for my model (Foo) and then deleting it. After all this, I finally did another amplify push and things were happier.

I took a backup of the table and was able to manually restore it (because the table was deleted and recreated I couldn't do this automatically). I only had a few records so this was painful, but not horrible.

And after all that ... the items are not indexed by updatedAt ..... meaning they are not sorted correctly. I don't even see the primary index .... what a frustrating day.

[malcomm]

OK so I just realized that I was not actually deleting the primary index (I don't think there's really a way to do this??? - not sure why that's not supported by DynamoDB???).

Just trying to add a simple sort key spawned a few other tickets:
#3349
aws-amplify/amplify-category-api#346
#3347

[edwardfoyle]

You are correct that once a DynamoDB table has been created, the primary key cannot be changed.

I'm going to close this issue and continue to follow up in #3347 because it seems like they are both converging to the same underlying problem.

[github-actions]

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.