I have a table with orders with the following model:
{
order_id: Hash Key
timestamp: Attribute
customer_id: Attribute
status: Attribute (new, pending, completed, cancelled)
}
I need to create a query that gets all orders by customer_id sorted by timestamp that aren't cancelled.
I have created an index with customer_id as the hash key and timestamp as the sort key. However, to retrieve orders with a particular status, I still need to use scan instead of query. Is it possible to achieve the result only with query? If not, will the scan actually utilize the fact that there is a hash key in a query, i.e., will it first query by the customer_id and then filter by order status, or will it just comb through all orders anyway?
Assuming you're specifying a KeyConditionExpression: "customer_id = :customerId", as shown in the docs then yes, only that customer's data will be scanned.
Given your access requirement, you should consider a composite hash or sort key on your index that includes status.
For example, customerId = "1111"
have either a hash key of "1111#COMPLETED" or a sort key of "COMPLETED#2024-03-19-12:05:00.000Z"
Either one would then support a Query operation looking at only the orders with the status you are interested in.
Which way you go depends on rather or not you might want all orders for a customer regardless of status.