建立分页游标 [英] Building a pagination cursor

问题描述

我的活动存储在图形数据库中.在某些情况下，将多个活动分组并汇总为1个活动.

I have activities that are stored in a graph database. Multiple activities are grouped and aggregated into 1 activity in some circumstances.

已处理的活动供稿看起来可能像这样:

A processed activity feed could look like this:

Activity 1

Activity 2

Grouped Activity
  Activity 3
  Activity 4

Activity 5

• 活动具有更新的时间戳和唯一的ID.

  • Activities have an updated timestamp and a unique id.

    活动按其更新时间排序，如果是分组活动，则使用其子活动中的最新更新时间.

    The activities are ordered by their updated time and in the case of a grouped activity, the most recent updated time within its child activities is used.

    活动可以插入列表中的任何位置(例如，如果我们开始关注某人，则他们过去的活动将被插入列表中.)

    Activities can be inserted anywhere in the list (for example, if we start following someone, their past activities would be inserted into the list).

    活动可以从列表中的任何位置删除.

    Activities can be removed from anywhere in the list.

    由于数据量大，使用微秒级的时间戳仍可能导致冲突(两个项目可以具有相同的时间戳).

    Due to the amount of data, using the timestamp with microseconds can still result in conflicts (2 items can have the same timestamp).

    光标标识符应该唯一且稳定.添加和删​​除Feed项目不应更改标识符.

    Cursor identifiers should be unique and stable. Adding and removing feed items should not change the identifier.

    我想介绍基于光标的分页，以允许客户端通过类似于 Twitter的.关于它们的构建方式似乎没有太多信息，因为我只发现了这个

    I would like to introduce cursor based paging to allow clients to paginate through the feed similar to twitter's. There doesn't seem to be much information on how they are built as I have only found this blog post talking about implementing them. However it seems to have a problem if the cursor's identifier happens to be pointing to the item that was removed.

    使用上述方法，如何生成可以用作上述方法的游标的标识符?最初，我考虑将时间戳与唯一的ID:1371813798111111.myuniqueid组合在一起.但是，如果删除1371813798111111.myuniqueid处的项目，则可以获取带有1371813798111111时间戳的项目，但无法确定应该从哪个时间戳开始的项目.

    With the above, how can I produce an identifier that can be used as a cursor for the above? Initially, I considered combining the timestamp with the unique id: 1371813798111111.myuniqueid. However, if the item at 1371813798111111.myuniqueid is deleted, I can get the items with the 1371813798111111 timestamp, but would not be able to determine which item with that timestamp I should start with.

    我的另一种方法是给每个提要结果分配一个递增的数字.由于数字按顺序递增，因此如果缺少数字/id，我可以选择下一个.但是，这样做的问题是，如果我开始在提要的中间删除和添加提要项，则游标ID将会更改.我对此问题的一个解决方案是在每个数字之间留出巨大的差距，但是很难确定如何以确定的方式将新项目添加到每个数字之间的空间中.此外，随着新项目的添加以及空白的填补，我们最终会遇到相同的问题.

    Another approach I had was to assign an incrementing number to each feed result. Since the number is incrementing and in order, if the number/id is missing, I can just choose the next one. However, the problem with this is that the cursor ids will change if I start removing and adding feed items in the middle of the feed. One solution I had to this problem is to have a huge gap between each number, but it is difficult to determine how new items can be added to the space between each number in a deterministic way. In addition, as the new items are added, and the gaps are being filled up, we would end up with the same problem.

    简单地说，如果我有一个项目列表，可以在列表中的任何位置添加和删除项目，那么为每个列表项目生成ID的最佳方法是什么，例如被删除，我仍然可以确定其在列表中的位置?

    推荐答案

    您需要具有其他(或现有)列，该列对于目标表中的每个新添加的行都按顺序增加.我们将此列称为 seq_id .

    You need to have additional (or existing) column which sequentially increased for every new added row to target table. Let's call this column seq_id.

    当客户端第一次请求游标时:

    When client request cursor for the first time:

    GET /api/v1/items?sort_by={sortingFieldName}&size={count}
    

    其中sortingFieldName是我们应用排序依据的字段的名称

    where sortingFieldName is name of field by which we apply sorting

    幕后发生的事情:

    SELECT * FROM items
    WHERE ...            // apply search params
    ORDER BY sortingFieldName, seq_id
    LIMIT :count
    

    响应:

    {
        "data": [...],
        "cursor": {
            "prev_field_name": "{result[0].sortingFieldName}",
            "prev_id": "{result[0].seq_id}",
            "nextFieldName": "{result[count-1].sortingFieldName}",
            "next_id": "{result[count-1].seq_id}",
            "prev_results_link": "/api/v1/items?size={count}&cursor=bw_{prevFieldName}_{prevId}",
            "next_results_link": "/api/v1/items?size={count}&cursor=fw_{nextFieldName}_{nextId}"       
        }
    }
    

    如果我们检索到的行数少于计数，则光标的下一个将不会出现.

    Next of cursor will not be present in response if we retrieved less than count rows.

    如果我们在请求中没有游标或没有要返回的数据，则游标的前一部分将不显示.

    Prev part of cursor will not be present in response if we don't have cursor in request or don't have data to return.

    当客户再次执行请求时-他需要使用游标.前进光标:

    When client perform request again - he need to use cursor. Forward cursor:

    GET /api/v1/items?size={count}&cursor=fw_{nextFieldName}_{nextId}
    

    幕后发生的事情:

    SELECT * FROM items
    WHERE ...            // apply search params
    AND ((fieldName = :cursor.nextFieldName AND seq_id > :cursor.nextId) OR 
          fieldName > :cursor.nextFieldName)
    ORDER BY sortingFieldName, seq_id
    LIMIT :count
    

    或向后光标:

    GET /api/v1/items?size={count}&cursor=fw_{prevFieldName}_{prevId}
    

    幕后发生的事情:

    SELECT * FROM items
    WHERE ...            // apply search params
    AND ((fieldName = :cursor.prevFieldName AND seq_id < :cursor.prevId) OR 
          fieldName < :cursor.prevFieldName)
    ORDER BY sortingFieldName DESC, seq_id DESC
    LIMIT :count
    

    响应将与上一个相似

    这篇关于建立分页游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！