CODEWITHSUNDEEP
amazon-dynamodbdynamodb-queriesamazon-dynamodb-index
showtime
showtime

Reputation: 1472

How to query with multiple conditions, limit and order by in DynamoDB?

So I have a table with the following data:

id                                   business_id   credit  name      owes     partner_id  type_id  updated

45b4bvfdghfghbdgfgfhbdfghbdbbfg       435634652     0       FORL      55      69992528       3  1652260271000
fghfdhbf657asdfsf43454356487768       435634652     0       FORL      77      69992528       3  1652529600000
fghfdhbf657a4sdf4365344565456487768   435634652     0       Hylde     65      69992528       2  1652529600000
fghfdhbf657a4564564565456487768       435634652     0       CC         5      69992528       1  1652529600000

And I am querying them based on the latest updated by type_id, partner_id and business_id. The query below is run on a loop:

 $result = $transaction_db->executeStatement([
    'Limit' => 1,
    'Statement' => 'SELECT * 
    FROM "items"."business_id-updated-index" 
    WHERE business_id = ' . $user_company_id . ' AND type_id = ' . intval($type['id']) . ' AND partner_id = ' . $data['cvr'] . ' 
   ORDER BY updated DESC',
]);

I receive only this row:

fghfdhbf657a4564564565456487768       435634652     0       CC         5      69992528

If I remove the limit it does fetch everything but thats not what I want. When this table grows to milions of items, thats not going to be a cheap thing to do, thats why I really need the LIMIT.

I do have GSI with sort keys as well: enter image description here

I am new to this, if I havent explained something clear enough please let me know.

Update:

As Charles has answered below, I tried adding a composite index by going to the AWS DynamoDB console and typing business_id#type_id#partner_id in the partition key and updated in the sort key. I named the index like this business_id-type_id-partner_id-updated-index but now it throws this error:

ValidationException: Must have at least one non-optional hash key condition in WHERE clause when using ORDER BY clause.

Upvotes: 0

Views: 518

Answers (1)

Charles
Charles

Reputation: 23823

In this particular case, since you are looking at equalities...

You could build a GSI with a PK that uses a composite value of business_id#type_id#partner_id

so for example 435634652#5#69992528

The sort key would be the updated attribute.

Upvotes: 0

Related Questions