Dynamoose - cannot query and where with 2 keys

Question

I have opened a related issue on GitHub, but maybe someone here will be able to help quicker.

Summary:

ValidationException: Query key condition not supported I need to find records in last (amount) seconds on a given location. Pretty simple, but already related to other issues: One and another one

WORKS:

Activity.query('locationId').eq(locationId).exec();

DOES NOT WORK:

Activity.query('locationId').eq(locationId).where('createdAt').ge(date).exec();

Code sample:

Schema

const Activity = (dynamoose: typeof dynamooseType) => dynamoose.model<ActivityType, {}>('Activity',
    new Schema({
      id: {
        type: String,
        default: () => {
          return uuid();
        },
        hashKey: true,
      },
      userId: {
        type: String,
      },
      locationId: {
        type: String,
        rangeKey: true,
        index: {
          global: true,
        },
      },
      createdAt: { type: Number, rangeKey: true, required: true, default: Date.now },
      action: {
        type: Number,
      },
    }, {
      expires: 60 * 60 * 24 * 30 * 3, //  activity logs to expire after 3 months
    }));

Code which executes the function

Funny part is that I found this as workaround proposed to be used until they merge PR giving ability to specify timestamps as keys, but unfortunately it does not work.

async getActivitiesInLastSeconds(locationId: string, timeoutInSeconds: number) {
    const Activity = schema.Activity(this.dynamoose);
    const date = moment().subtract(timeoutInSeconds, 'seconds').valueOf();
    return await Activity.query('locationId').eq(locationId)
      .where('createdAt').ge(date).exec();
  }

score 5 · Accepted Answer · answered Sep 22 '18 at 11:01

5

I suspect createdAt is not a range key of your table / index. You need to either do .filter('createdAt').ge(date) or modify your table / index schema.

answered Sep 22 '18 at 11:01

gerrytan

40,313
9
84
99

Please look at code: `createdAt: { type: Number, rangeKey: true, required: true, default: Date.now },` It is range key. – Marek Urbanowicz Sep 22 '18 at 11:07
@gerrytan is totally correct. I also submitted an answer that gives a bit more detail about how to change your index to set `createdAt` as a range key. But doing the filter method should work as well. – Charlie Fish Sep 22 '18 at 23:09

score 1 · Answer 2 · answered Sep 22 '18 at 23:08

1

I'm pretty sure the problem is that when you specifying rangeKey: true on the createdAt property you are telling that to be used on the global index (I don't think that is the correct term). That range key will be linked to the id property.

I believe the easiest solution would be to change your locationId index to be something like the following:

index: {
    global: true,
    rangeKey: 'createdAt',
},

That way you are being very explicit about which index you want to set createdAt as the rangeKey for.

After making that change please remember to sync your changes with either your local DynamoDB server or the actual DynamoDB service, so that the changes get populated both in your code and on the database system.

Hopefully this helps! If it doesn't fix your problem please feel free to comment and I'll help you further.

answered Sep 22 '18 at 23:08

Charlie Fish

18,491
19
86
179

1

Didn't know I can specify range key for the index. Will check it tomorrow (it's Sunday :) ) and let you know! – Marek Urbanowicz Sep 23 '18 at 06:38
Hi. It is working well, but only for one key... (but still big thank you!) I am struggling to make query: `language: { type: String, index: { global: true, rangeKey: 'channel', }, }, channel: { type: String, rangeKey: true, index: { global: true, rangeKey: 'messageCode', }, }, messageCode: { type: String, rangeKey: true, },` – Marek Urbanowicz Oct 01 '18 at 13:24
`TranslationsModel.query('language').eq('EN') .where('channel').eq(NotificationChannel.Email) .and() .where('messageCode').eq('TEST_MSG') .exec();` – Marek Urbanowicz Oct 01 '18 at 13:25
I can do where channel .. but when I add `and().where('messageCode') it fails with `Query condition missed key schema element` Any great suggestions as before? – Marek Urbanowicz Oct 01 '18 at 13:26
@MU The [`.where()`](https://dynamoosejs.com/api#querywhererangekey) is only for range keys. Try using the [`.filter()`](https://dynamoosejs.com/api#queryfilterfilter) instead. – Charlie Fish Oct 01 '18 at 15:24

Dynamoose - cannot query and where with 2 keys

Summary:

Code sample:

2 Answers2