To query on two fields

Question

I have a Dynamoose (DynamoDB) model called PromoCode with a schema that looks like this:

{
  promoCode: {
    hashKey: true,
    type: String,
  },
  previouslyUsed: {
    type: Boolean,
    default: false,
    index: {
      global: true,
      name: 'previouslyUsedIndex',
    },
  },
  promoGroup: {
    type: String,
    index: {
      global: true,
      name: 'promoGroupIndex',
    },
  },
}

Essentially, I have a table full of promo codes and I want to get a single promo code that hasn't been used and is part of a particular "group" of promo codes.

So, I want to query on both previouslyUsed and promoGroup fields and limit the results to a single results. This is what I came up with:

PromoCode.query('previouslyUsed').eq(false)
    .and()
    .filter('promoGroup').eq('friend').limit(1)

This returns no results, even though I know that the query should match a result. If I increase the limit to 10, then I get back four results. This makes me think that the limit is happenning before the and() thus the preceding filter() is only filtering on the 10 returned results where previouslyUsed=false.

How do I take a single result where the conditions previouslyUsed=false and promoGroup=friend are valid?

I am wondering how you are getting the result? Actually, you should get error when you try to use query API without providing the key attribute 'promoCode' on it. Are you querying the index? — notionquest, Oct 16 '17 at 17:04
The only thing I did not include was an `exec()` at the tail end of the chain. This gives me a promise which I can use. I do not understand why I would get an error. Do you have additional insight? — KJ Price, Oct 16 '17 at 17:10
Do you have two different indexes with one index having hash key has boolean? Look like you are querying the index which has boolean hash key which can have only two values. Can you see how many items present in previouslyusedindex and what is the read capacity unit? — notionquest, Oct 16 '17 at 18:00

score 2 · Answer 1 · answered Oct 16 '17 at 20:04

So, here's what I figured out (to answer my own question). Firstly, using filter will only filter the results that are pulled from the database. So, I wasn't experiencing some weird bug.

Secondly, what I really wanted was a range key setup. This will give me the following schema:

{
  promoCode: {
    hashKey: true,
    type: String,
  },
  previouslyUsed: {
    type: Boolean,
    default: false,
    index: {
      global: true,
      name: 'previouslyUsedIndex',
      rangeKey: 'promoGroup',
    },
  },
  promoGroup: {
    type: String,
    rangeKey: true,
    index: true,
  },
}

Note the use of both instances of rangeKey above. Evidently both are necessary to do the following query:

PromoCode.query('previouslyUsed').eq(false)
    .where('promoGroup').eq('friend')

It's actually as "simple" as that. This let's me filter on two different fields.

To query on two fields

1 Answers1