基于游标分页数据_使用基于游标的分页对实时数据进行分页

基于游标分页数据

Pagination is a technique for breaking large record sets into smaller portions called pages. As a developer, you should be familiar with implementing pagination, but implementing pagination for real time data can become tricky even for experienced developers. In this tutorial, we are going to discuss the practical use cases and solutions for real time data pagination and cursor based pagination.

分页是一种将大型记录集分解为称为页面的较小部分的技术。 作为开发人员，您应该熟悉实现分页，但是即使对于有经验的开发人员，对实时数据实现分页也会变得棘手。 在本教程中，我们将讨论实时数据分页和基于光标的分页的实际用例和解决方案。

识别实时数据分页中的问题 (Identifying Issues in Real Time Data Pagination)

Wikipedia defines real time data as information delivered immediately after collection. There is no delay in the timeliness of the information provided. In such applications, it’s difficult to provide accurate paginated data due to the frequent updates. Let’s take a look at the issues with standard pagination when managing real time data.

维基百科将实时数据定义为收集后立即传递的信息。 所提供信息的及时性没有延迟。 在此类应用程序中，由于频繁更新，因此很难提供准确的分页数据。 让我们看一下管理实时数据时标准分页的问题。

• Assumes the data is static and doesn’t change frequently – In default pagination, a retrieved record set is split into a number of pages. As data is not frequently changed, users feel like the pagination is working accurately, but results of the pagination become inaccurate when adding new data or removing existing data.

  假设数据是静态的并且不经常更改 –在默认的分页中，检索到的记录集被分为多个页面。 由于数据不经常更改，因此用户感觉分页工作正常，但是在添加新数据或删除现有数据时分页结果变得不准确。

• Pagination only considers record count, instead of each individual record – Records are broken into pages using the total record count and paginated normally. It doesn’t consider whether each record falls into the right page on pagination. This can lead to a redundant display of records.

  分页仅考虑记录数，而不考虑每个单独的记录 –使用总记录数将记录分为几页，并按正常分页。 它不考虑每条记录是否在分页中都落入正确的页面。 这可能导致记录的冗余显示。

Considering these points, it’s difficult to use to default pagination techniques to handle real time data. Let’s try to identify the issues using a practical scenario.

考虑到这些要点，很难使用默认的分页技术来处理实时数据。 让我们尝试使用实际方案来确定问题。

Assume that we have 20 records initially and we using 10 as the limit to break the records into pages. The following image shows how records are broken into pages.

假设我们最初有20条记录，并且使用10条限制将记录分成页面。 下图显示了如何将记录分为页面。

Now assume that the result set is updated by five new records while we are on the first page. The following image shows the current scenario.

现在，假设我们在首页上时，结果集由五个新记录更新。 下图显示了当前方案。

Now we navigate to the second page. Based on our first image, it should retrieve the records from 1-10. However, records with numbers 15-6 will be retrieved. You can clearly see that record numbers 15-11 are displayed in both the first page as well as the second page.

现在，我们导航到第二页。 根据我们的第一张图片，它应该从1-10检索记录。 但是，将检索编号为15-6的记录。 您可以清楚地看到记录号15-11在第一页和第二页中都显示。

实时数据分页的实际用例 (Practical Use Cases of Real Time Data Pagination)

As we all know, reinventing the wheel is not something a developer should do. We should look at the existing pagination techniques of sites that solved these issues before thinking about building our own. Many social networking sites such as Twitter and Facebook provide real time data in their user profiles. In this section, we will be looking at the practical use cases of real time data pagination using some of the most popular sites.

众所周知，重塑车轮并不是开发人员应该做的事情。 在考虑构建自己的站点之前，我们应该查看解决这些问题的站点的现有分页技术。 Twitter和Facebook等许多社交网站在其用户配置文件中提供实时数据。 在本节中，我们将研究使用一些最受欢迎的站点进行实时数据分页的实际用例。

基于Twitter API光标的分页 (Twitter API Cursor Based Pagination)

Twitter user profiles are frequently populated with new tweets, so the Twitter time line data retrieval mechanism should be a good start to identifying pagination techniques in real time data feeds. Let’s see how it works using a Twitter API method.

Twitter用户个人资料经常填充新的推文，因此Twitter时间线数据检索机制应该是识别实时数据源中的分页技术的良好起点。 让我们看看如何使用Twitter API方法。

The following contains a sample request to the Twitter API search tweets method.

以下内容包含对Twitter API搜索推文方法的示例请求。

https://api.twitter.com/1.1/search/tweets.json?q=php&since_id=24012619984051000&max_id=250126199840518145&result_type=recent&count=10

In the above URL, we request the most recent tweets containing the word ‘php’ and break the result set into blocks of 10 using the count parameter. This is the typical behavior of offset pagination where we reply on record count. But here we can see two additional parameters called since_id and max_id, which enables cursor based pagination. Let’s see how cursor based pagination works using our earlier example.

在上述URL中，我们请求包含单词'php'的最新tweet，并使用count参数将结果集分成10个块。 这是偏移分页的典型行为，在此我们根据记录数进行答复。 但是在这里我们可以看到两个额外的参数，因为since_id和max_id ，它们启用了基于游标的分页。 让我们使用前面的示例来看看基于光标的分页如何工作。

We had 20 records broken into 2 pages and assume we are on the first page. 5 new records are added to the top of the list. The following image previews the current scenario.

我们将20条记录分为2页，并假设我们位于第一页。 5个新记录被添加到列表的顶部。 下图预览了当前方案。

Now let’s take a look at a part of the response generated for the first page of the Twitter search request. You can view the complete response format here.

现在，让我们看一下为Twitter搜索请求的第一页生成的部分响应。 您可以在此处查看完整的响应格式。

"search_metadata": {
  "max_id": 250126199840518145,
  "since_id": 24012619984051000,
  "refresh_url": "?since_id=250126199840518145&q=php&result_type=recent&include_entities=1",

  "next_results": "?max_id=249279667666817023&q=php&count=10&include_entities=1&result_type=recent",

  "count": 10,
  "completed_in": 0.035,
  "since_id_str": "24012619984051000",
  "query": "php",
  "max_id_str": "250126199840518145"
}

As you can see, the search_metadata section provides details about the results. It will generate the next_results URL, in case there are more records to paginate. We are mainly using the max_id parameter for pagination. With each response we will retrieve the max_id parameter and we can use it to generate next result set. We use the max_id parameter to receive results older than the given ID.

如您所见， search_metadata部分提供了有关结果的详细信息。 如果有更多记录要分页，它将生成next_results URL。 我们主要使用max_id参数进行分页。 对于每个响应，我们将检索max_id参数，并可以使用它生成下一个结果集。 我们使用max_id参数接收早于给定ID的结果。

In our example, we should retrieve the max_id parameter as Record 11 while displaying records 20-11. Then we pass the max_id to generate next result set. Thus, we will get the accurate results as shown in following image.

在我们的示例中，我们应该在显示记录20-11时将max_id参数检索为记录11。 然后，我们传递max_id以生成下一个结果集。 这样，我们将获得准确的结果，如下图所示。

As you can see, we have the accurate results for the second page by eliminating 15 records at the top instead of 10 in offset based pagination. In cursor based pagination we can’t consider the concept of pages, as it changes rapidly, so the results will be considered as either previous or next. Generally, max_id is effective enough to generate accurate results, but there can be scenarios where since_id is also essential while paginating back and forth. You can look at more advanced examples of using both max_id and since_id on Twitter’s Developer section.

如您所见，我们通过消除顶部的15条记录(而不是基于偏移的分页10条)来获得第二页的准确结果。 在基于游标的分页中，我们无法考虑页面的概念，因为页面的变化很快，因此结果将被视为上一个或下一个。 通常， max_id足以产生准确的结果，但是在since_id情况下，来回分页时， since_id也很重要。 您可以在Twitter的Developer部分中查看同时使用max_id和since_id更高级的示例。

基于Facebook API游标的分页 (Facebook API Cursor Based Pagination)

Facebook’s API implementation is slightly different compared to Twitter, even though both APIs use the same theory. Let’s take a look at the response for a sample Facebook API request.

尽管两个API都使用相同的理论，但与Twitter相比，Facebook的API实现略有不同。 让我们看一下示例Facebook API请求的响应。

{
  "data": [
     ... Endpoint data is here
  ],
  "paging": {
    "cursors": {
      "after": "MTAxNTExOTQ1MjAwNzI5NDE=",
      "before": "NDMyNzQyODI3OTQw"
    },
    "previous": "https://graph.facebook.com/me/albums?limit=25&before=NDMyNzQyODI3OTQw"
    "next": "https://graph.facebook.com/me/albums?limit=25&after=MTAxNTExOTQ1MjAwNzI5NDE="
  }
}

As you can see, Facebook uses two string based cursors called before and after, for pagination, instead of since_id and max_id. In Facebook, the before cursor will point to the start of the page while the after cursor points to the end of the page.

如您所见，Facebook使用两个基于字符串的游标，分别before和after进行分页，而不是since_id和max_id 。 在Facebook中，在before光标将指向页面的开始，而after光标指向页面的末端。

Most API’s with real time data use this mechanism to accurately paginate through their results. As developers, we need to know the theory behind cursor based pagination in order to use existing APIs as well as create our own when necessary.

大多数具有实时数据的API都使用此机制来准确地对结果进行分页。 作为开发人员，我们需要了解基于游标的分页背后的理论，以便使用现有的API并在必要时创建自己的API。

建立实时数据分页的基础 (The Basics of Building Pagination for Real Time Data)

Implementing real time data pagination is a complex task beyond the scope of this tutorial, so we are going to look at the basic needs and the process of creating a simple pagination mechanism to understand cursor based pagination.

实现实时数据分页是超出本教程范围的复杂任务，因此，我们将研究基本需求以及创建简单分页机制以理解基于光标的分页的过程。

Let’s identify the basic components of cursor based pagination, using the previously discussed examples.

让我们使用前面讨论的示例来确定基于光标的分页的基本组件。

• Cursors – we need to have at least one column with unique sequential values to implement cursor based pagination. This can be similar to Twitter’s max_id parameter or Facebook’s after parameter.

  游标–我们需要至少有一列具有唯一顺序值的列，以实现基于游标的分页。 这可以类似于Twitter的max_id参数或Facebook的after参数。

• Count – we need the count parameter as with offset based pagination for filtering a limited number of results, before or after the cursor.

  计数–我们需要使用基于偏移量的分页的count参数来过滤游标之前或之后的有限数量的结果。

• Next URL – This is needed in case we are providing the pagination through an API. Users need to know whether the next page is available and how to get the next data set.

  下一个URL –如果我们通过API提供分页，则需要此URL。 用户需要知道下一页是否可用以及如何获取下一个数据集。

• Previous URL – This is needed in case we are providing the pagination through an API. Users need to know whether the previous page is available and how to get the next data set.

  上一个URL –如果我们通过API提供分页，则需要此URL。 用户需要知道上一页是否可用以及如何获取下一个数据集。

These are the basic needs for cursor based pagination. Developers often work with offset based pagination and rarely get a chance to work with cursor based pagination, so it’s important to identify the differences and benefits of each technique for using them in appropriate scenarios.

这些是基于光标的分页的基本需求。 开发人员经常使用基于偏移量的分页，很少有机会使用基于光标的分页，因此，确定在适当的场景中使用每种技术的区别和好处非常重要。

• In offset pagination, we can sort by any column and paginate the results while cursor based pagination depends on the sorting of the unique cursor column.

  在偏移分页中，我们可以按任何列排序并分页结果，而基于光标的分页取决于唯一光标列的排序。

• Offset pagination contains page numbers in addition to next and previous links. But due to the highly dynamic nature of the data, we can’t provide page numbers for cursor based pagination.

  偏移分页除了下一个和上一个链接之外，还包含页码。 但是由于数据的高度动态性，我们无法为基于光标的分页提供页码。

• Generally, offset pagination allows us to navigate in both directions while cursor based pagination is mostly used for forward navigation.

  通常，偏移分页允许我们在两个方向上导航，而基于光标的分页主要用于前向导航。

So far, we looked at the basic needs and differences of cursor based pagination. Now we can move into a sample implementation to identify how it works.

到目前为止，我们研究了基于游标的分页的基本需求和差异。 现在，我们可以进入一个示例实现来确定其工作方式。

实现基于游标的分页 (Implementing Basic Cursor Based Pagination)

<?php

class Real_Time_Pagination{
  public $conn;
  public function dbConnection(){
    $this->conn = new PDO('mysql:host=localhost;dbname=database','username','password');		
  }

  public function handlePaginationData(){
    $direction = 'next';
    $order = 'desc';
    $where = '';
    $params = array();

    if(isset($_GET['max_id'])){
      $direction = 'next';
      $where = " where tweetID < :max_id ";
      $order = 'desc';
      $params = array(':max_id' => $_GET['max_id']);


    }else if(isset($_GET['since_id'])){
      $direction = 'prev';
      $where = " where tweetID > :since_id ";
      $order = 'asc';
      $params = array(':since_id' => $_GET['since_id']);
    }

    $sth = $this->conn->prepare("select * from tweets $where order by tweetID $order  ");
    $sth->execute($params);
    $results = $sth->fetchAll();

    $count = count($results);

    $sth = $this->conn->prepare("select * from tweets $where order by tweetID $order  limit 3");
    $sth->execute($params);
    $results = $sth->fetchAll();

    if($direction == 'prev'){
      $results = array_reverse($results);
    }

    $html = "";

    $max_id = '';
    $since_id = '';

    foreach($results as $row) {
      if($since_id == '' )
        $since_id = $row['tweetID'];

	$view = $this->paginateDataView();
        $html .=  $this->assignTemplateVars(array('tweets'=>$row['tweet']) , $view);

	$max_id = $row['tweetID'];
      }

      $html  = $this->getResultsList($html);

      $html .= $this->paginator($max_id,$since_id,$count,$direction); 

      return $html;
	
  }	

  public function getResultsList($res){

    $html = "<table>$res</table>";
    return $html;
  }

  public function assignTemplateVars($params,$view){
    foreach($params as $key=>$val){
      $view = str_replace('{'.$key.'}',$val,$view);
    }		
    return $view;
  }
}

• First, we create the database connection using PDO. Then we execute the handlePaginationData function for paginating the results.

  首先，我们使用PDO创建数据库连接。 然后，我们执行handlePaginationData函数对结果进行分页。

• Then we check whether max_id or min_id parameter is available in URL. max_id is is similar to Facebook’s after parameter and used to navigate forward. min_id is similar to Facebook’s before parameter and used to navigate backwards. Also, we set up the navigation direction, the where clause using max_id or min_id and the sorting order.

  然后，我们检查URL中的max_id或min_id参数是否可用。 max_id与Facebook的after参数相似，用于向前导航。 min_id与Facebook的before参数相似，用于向后导航。 同样，我们设置导航方向，使用max_id或min_id的where子句以及排序顺序。

• Then we execute the query to get the complete result count followed by the same query with a limit statement to narrow the results.

  然后，我们执行查询以获取完整的结果计数，然后执行带有限制语句的相同查询以缩小结果。

• In case we are traversing in the previous direction, we have to change the sorting to asc. Otherwise it will retrieve the most recent records instead of the previous page. We reverse the records in array to show them as descending.

  如果我们沿前一个方向遍历，则必须将排序更改为asc。 否则，它将检索最新记录，而不是前一页。 我们反转数组中的记录以将它们显示为降序。

• Then we loop through the results. While looping, we assign the ID of the first record as min_id and last record as max_id. These cursor values are used for filtering accurate data by eliminating duplication.

  然后我们遍历结果。 循环时，我们将第一个记录的ID分配为min_id ，最后一个记录的ID分配为max_id 。 这些游标值用于通过消除重复来过滤准确的数据。

• Finally, we can look at the paginator function for implementing pagination links.

  最后，我们可以看一下实现分页链接的paginator功能。

public function paginator($max_id,$since_id,$count,$direction){
  $pag['next_url'] = "?max_id=".$max_id;
  $pag['prev_url'] = "?since_id=".$since_id;
	
  $html = '';
  $params = array('prev_url'=> $pag['prev_url'], 'next_url' => $pag['next_url']);

  if($direction == 'next' ){

    if($count <= 3)
      $params['next_url'] ='#';
	
      $view   = $this->paginateLinksView();
      $html  .= $this->assignTemplateVars($params,$view);
		
  }

  if($direction == 'prev'){	
    if($count <= 3)
      $params['prev_url'] ='#';

      $view   = $this->paginateLinksView();
      $html  .= $this->assignTemplateVars($params,$view);
  }
  return $html;
}

public function paginateLinksView(){
  $html   ="<a  href='{prev_url}'>Prev</a>";
  $html  .="<a  href='{next_url}'>Next</a>";
		
  return $html;
}
public function paginateDataView(){

  $html = "<tr><td>{tweet}</td></tr>";
		return $html;
}

The following code contains the initialization code for the pagination generated in this section.

以下代码包含本节中生成的分页的初始化代码。

<?php
$rtp = new Real_Time_Pagination();
$rtp->dbConnection();
echo $rtp->handlePaginationData();

Now we have a simple data pagination sample to understand how real time data pagination works. Use this code and paginate through the results. While paginating, add some records at the end of table to make it real time. Then paginate forward and backwards to to check data duplications in pages. Do the same with offset based pagination to understand the difference.

现在，我们有一个简单的数据分页示例，以了解实时数据分页的工作原理。 使用此代码并在结果中分页。 分页时，在表末尾添加一些记录以使其实时。 然后向前和向后分页以检查页面中的数据重复。 对基于偏移量的分页执行相同操作以了解差异。

结论 (Conclusion)

In this tutorial, we learned the theory behind real time data pagination with cursor based pagination. Let us know your thoughts and experiences in the comments below!

在本教程中，我们学习了基于光标的分页的实时数据分页的原理。 在下面的评论中让我们知道您的想法和经验！

翻译自: https://www.sitepoint.com/paginating-real-time-data-cursor-based-pagination/

基于游标分页数据