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

问题描述

这是我的DynamoDB当前数据：

Here is my DynamoDB current data:

我的目标是创建一个查询，该查询将结果过滤到组集中（类似于默认），然后按优先级排序，然后将结果过滤到loginIn == true和status == idle的情况。

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中就像

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}}

我尝试过的事情：

下面是我当前的代码，因此远：

Below is my current code so far:

 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.

注意：状态和组为显然是保留关键字，因此我不得不使用 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   |
+--------+---------+-------+----------+----------+--------+

• 分区/ hash键：userId


• 排序键：group



在（组，优先）。这将用于查询。是的，为此索引选择的组合将具有重复项：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和优先级> =：priority 完成查询，在 status ='idle'上过滤，并且loggingIn = true
  
  
  
  
  • 变体是根据状态或已登录，因为您可以使用它们进行过滤，这有助于使查询更具选择性，然后在客户端上按优先级进行排序
  
  
  • Updating an user on this tables requires update/insert as many rows as there are groups the user belongs too;
  • Removing an user means removing all items for the user.
  • Querying is done by group = :group and priority >= :priority, filtering on status = 'idle' and loggedIn = true
    • a variant is to sort on status or loggedIn, because you filter with them, this help making the query a bit more selective, then sort on priority on the client

    我应该遵循这种方法吗？我认为如果有多个组并且一个组包含多达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屋！