按优先级排序结果集团价值观然后过滤结果 [英] Order Results by Priority & Group Values & then filter results

问题描述

这是我的 DynamoDB 当前数据:

Here is my DynamoDB current data:

我的目标是创建一个查询，该查询过滤组集中的结果(喜欢默认")，然后按优先级排序，然后将结果过滤到已登录 == true 和状态 == 空闲的那些.

My goal is to create a query which filters the result in the Group Set (to like "Default") , and then sorts by priority and then filters the results to those where loggedIn == true and status == idle.

在 SQL 中是这样的

In SQL it would be something like

SELECT * 
FROM userstatustable 
WHERE group == "default" 
  AND loggedIn == true AND status == "idle" 
ORDER BY priority DESC 
LIMIT 1

我将如何创建查询来执行此操作?

How would I create a query to do this?

下面是我对 DynamoDB 表的 serverless.yml 文件描述.

Below is my serverless.yml file description of the DynamoDB table.

userStatusTable: #This table is used to track a users current status.
      Type: AWS::DynamoDB::Table
      Properties:
        TableName: ${self:custom.userStatusTable}
        AttributeDefinitions: #UserID in this case will be created once and constantly updated as it changes with status regarding the user.
          - AttributeName: userId
            AttributeType: S
        KeySchema:
          - AttributeName: userId
            KeyType: HASH
        ProvisionedThroughput:
            ReadCapacityUnits: ${self:custom.dynamoDbCapacityUnits.${self:custom.pstage}}
            WriteCapacityUnits: ${self:custom.dynamoDbCapacityUnits.${self:custom.pstage}}

我尝试过的事情:

以下是我目前的代码:

 const userStatusParams = {
        TableName: process.env.USERSTATUS_TABLE,
        FilterExpression: "loggedIn = :loggedIn and #s = :status and contains(#g,:group) ",
        //Limit: 1,
        ExpressionAttributeValues: {
          ":loggedIn": true,
          ":status" : "idle",
          ":group" : "DEFAULT"
        },
        ExpressionAttributeNames: {"#s": "status","#g" : "group"}
      };
      var usersResult;
      try {
        usersResult = await dynamoDbLib.call("scan", userStatusParams);
        console.log(usersResult);
      }catch (e) {
        console.log("Error occurred querying for users belong to group.");
        console.log(e);
      }

这使用扫描并且能够返回所有符合条件的结果......但是它不会按优先级对结果进行排序降序.

This uses scan and is able to return all the results that meet the criteria ... however it does not sort the results by priority in descending order.

注意:status 和 group 显然是保留关键字，所以我不得不使用 ExpressionAttributeNames 来说明这一点.另请注意，此表最终将有数千名用户.

Note: status and group are reserved keywords apparently so I had to use ExpressionAttributeNames to account for that. Also note that this table will have thousands of users in it eventually.

推荐答案

链接到我的其他答案 按设计使用主表.

Link to my other answer that uses the main table as designed.

这种方法需要修改 UserStatus 中的 group 建模，从带有字符串的单个记录设置为带有字符串的多条记录.这是因为 DynamoDB 不支持(不过，这是一个很好的功能请求)对集合进行键控.

This approach requires modifying group modeling in UserStatus from a single record with a string set to multiple records with a string. This is because DynamoDB doesn't support (yet, that makes for a good feature request though) keying on sets.

主表用于更新/插入/删除，如下所示:

The main table is used for updates/inserts/deletes and looks like this:

+--------+---------+-------+----------+----------+--------+
| userId | group   | type  | priority | loggedIn | status |
+--------+---------+-------+----------+----------+--------+
| 123    | default | admin | 1        | true     | idle   |
+--------+---------+-------+----------+----------+--------+
| 123    | orange  | admin | 1        | true     | idle   |
+--------+---------+-------+----------+----------+--------+
| 124    | default | admin | 3        | false    | idle   |
+--------+---------+-------+----------+----------+--------+
| 125    | orange  | admin | 2        | false    | idle   |
+--------+---------+-------+----------+----------+--------+

• 分区/哈希键:userId
• 排序键:组

在(组、优先级)上设置 GSI.这将用于查询.是的，为此索引选择的组合会有重复项:DynamoDB 不会为此烦恼，而且效果很好.

Setup a GSI on (group, priority). This will be used for queries. Yes, the combination chosed for this index will have duplicates: DynamoDB doesn't bother with this and works nicely.

+---------+----------+--------+-------+----------+--------+
| group   | priority | userId | type  | loggedIn | status |
+---------+----------+--------+-------+----------+--------+
| default | 1        | 123    | admin | true     | idle   |
+---------+----------+--------+-------+----------+--------+
| default | 3        | 124    | admin | false    | idle   |
+---------+----------+--------+-------+----------+--------+
| orange  | 1        | 123    | admin | true     | idle   |
+---------+----------+--------+-------+----------+--------+
| orange  | 2        | 125    | admin | false    | idle   |
+---------+----------+--------+-------+----------+--------+

任务:

• 更新此表上的用户需要更新/插入与用户所属的组一样多的行；
• 移除用户意味着移除该用户的所有项目.
• 查询由 group = :group and priority >= :priority 完成，过滤 status = 'idle' 和 loggedIn = true
  • 一种变体是按状态或登录进行排序，因为您使用它们进行过滤，这有助于使查询更具选择性，然后在客户端上按优先级排序

  我应该采用这种方法吗?我认为当有很多组并且单个组包含多达 20% 的总用户，并且用户属于 2 或 2 个组时，这是一个很好的设计.

  Should I follow this approach? I think it is a good design when there are many groups and a single group contains up to 20% of total users, and users belong to 2 or 2 groups.

  这篇关于按优先级排序结果集团价值观然后过滤结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！