如何在DynamoDB中使用多个条件，限制和顺序查询？

发布于 2025-02-09 09:21:55 字数 1778 浏览 2 评论 0原文

因此，我有一个具有以下数据的表：

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

我是根据Type_ID，pantert_id和business_id的最新更新来查询它们。下面的查询是在循环中运行的：

 $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',
]);

我只收到此行：

fghfdhbf657a4564564565456487768       435634652     0       CC         5      69992528

如果我删除limit它确实获取了所有内容，但这不是我想要的。当这张表长到数英里的物品时，那并不是一件便宜的事情，这就是为什么我真的需要限制的原因。

我也有GSI带有排序键：

我是新手，如果我还没有解释一些清晰的东西，请告诉我。

更新：

正如Charles在下面回答的那样，我尝试通过进入AWS DynamoDB控制台并键入business_id＃type_id＃partch_id＃partch_id，尝试添加复合索引，并在分区密钥和中添加。在排序密钥中更新。我将索引命名为business_id-type_id-partner_id-partner_id-partner_id-index，但现在丢弃了此错误：

验证exception：在使用订单时，必须在wery子句中至少具有一个非访问的关键条件。

原文

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:

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: