Creating a Cursor Based Pagination System in PHP

Question

I am trying to figure out how how to create my own simple cursor based pagination system in PHP and am having difficulty trying to understand how star starting_after and starting_before works as mentioned in this medium.com post for how the company stripe deals with cursor pagination. In my case I am using the id column that is in ascending order to hopefully make this work. The issue that I am having is getting an id for the first "page". Currently my first page would direct to the second page since starting_after leads to the next page and not the currently page. Any advice for how to build this out would be awesome. I already created page based pagination, but think that cursor pagination would be more useful for most of my cases.

I have attached the two files that I have created thus far to try to get this to work.

Pagination class

<?php

require_once "DB.php";

class New_Pagination {
  private $table = "";
  private $limit;
  private $starting_after = "";
  private $starting_before = "";
  private $db;

  public function __construct() {
    $this->db = DB::getInstance();
  }

  public function getLimit(): int {
    return $this->limit;
  }

  public function setLimit(int $limit): void {
    $this->limit = $limit;
  }

  public function getStartingAfter(): string {
    return $this->starting_after;
  }

  public function setStartingAfter(string $starting_after): void {
    $this->starting_after = $starting_after;
  }

  public function getStartingBefore(): string {
    return $this->starting_before;
  }

  public function setStartingBefore(string $starting_before): void {
    $this->starting_before = $starting_before;
  }

  public function getTable(): string {
    return $this->table;
  }

  public function setTable(string $table): void {
    $this->table = $table;
  }

  public function idExists($id) {
    $result = $this->db->find(self::getTable(), [
      "select" => "id",
      "conditions" => "id = $id",
      "fetchType" => "single"
    ]);
    if (empty($result)) {
      return FALSE;
    } else {
      return $result->id;
    }
  }

  public function getData($starting_after, $starting_before) {
    self::setStartingAfter($starting_after);
    self::setStartingBefore($starting_before);

    $starting_after = self::getStartingAfter();
    $starting_before = self::getStartingBefore();

    $data = [];
    $order = !empty($starting_after) ? "ASC" : "DESC";
    if (empty($starting_after) && empty($starting_before)) {
      $data["data"] = $this->db->find(self::getTable(), [
        "select" => "*",
        "order" => "id ASC",
        "limit" => self::getLimit(),
        "fetchType" => "all"
      ]);
    } else {
      $data["data"] = $this->db->find("carousel_image", [
        "select" => "*",
        "conditions" => "id >= '$starting_after' OR id <= '$starting_before'",
        "order" => "id $order",
        "limit" => self::getLimit(),
        "fetchType" => "all"
      ]);
    }
    $next = self::idExists($data["data"][count($data["data"]) - 1]->id + 1);
    $previous = self::idExists($data["data"][0]->id - 1);
    $data["cursor"] = [
      "next" => $next,
      "previous" => $previous
    ];
    return $data;
  }

  public function generateLink() {
    $test = self::getData("", "");

    $test2 = [];
    $test2[0] = $test;
    $i = 0;

    do {
      $test2[$i] = $test;
      $test = self::getData($test["cursor"]["next"], "");
      $i++;
      $test2[$i] = $test;
    } while ($test["cursor"]["next"] !== FALSE);
    $test2[$i] = $test;

    echo "<ul>";
    $j = 1;
    foreach ($test2 as $key => $val) {
      if ($val["cursor"]["next"] !== FALSE) {
        $url = "/process.php?starting_after=" . $val["cursor"]["next"];
        echo "<li>";
        echo "<a href='$url'>$j</a>";
        echo "</li>";
        $j++;
      }
    }
    echo "<ul>";
  }
}

Test file

$pagination = new New_Pagination();

$pagination->setLimit(2);
$pagination->setTable("carousel_image");
echo "<pre>";

$pagination->generateLink();

echo "</pre>";

Answer 1

The cursors are useful to prevent scan big tables and allow to move in very big sources (files, external resources, etc., etc.). In the majority of the cases, cursors are provided by binary libraries and supported by the core of the related system (mysql, files). If you try to emulate this behavior in not natural way you must take care because you could add overhead and get unexpected results.

In the other hand, is very useful to have a pagination class, but be aware, this class have some problems.

1. getData is very expensive because it performs 3 queries to get a batch of results
2. the class is too verbose, the getters and setters add too much noise
3. the results are sorted using very rare criteria. Remember, if you are in search results you need the same order moving to the next page and moving to the previous page

My suggestions... Create interfaces

<?php

interface CursorAble {
    
    public function fetchNext ($startingAfter);
    public function fetchPrev ($startingBefore);
    public function getPreviousLink ();
    public function getNextLink ();
}



interface Pageable {
    
    public function getCollectionSize ();
    public function getPageSize ();
    public function getPagesCount ();
    public function getPageLinks ();
}

When you create interfaces you ensures that the classes expose the desired behavior and furthermore delegate the specialized details to the concrete implementations. The concrete implementations can define it dependencies in the constructor, something very good when you relies on dependency injection.

CursorAble implementation example

<?php

class PdoCursorAbleTable implements CursorAble {

    private $pdo;
    private $table;
    private $results;
    private $pageSize;

    public function __construct (PDO $pdo, $table, $pageSize = 100) {
        $this->pdo      = $pdo;
        $this->table    = $table;
        $this->pageSize = (int)$pageSize ?: 100;
    }

    public function fetchNext ($startingAfter) {
        $s = $this->pdo->prepare("select * from {$this->table} where id > :starting_after limit {$this->pageSize}");
        $s->bindValue(':starting_after', $startingAfter, PDO::PARAM_INT);
        $s->execute();
        $this->results = $s->fetchAll() ?: [];

        return $this->results;
    }

    public function fetchPrev ($startingBefore) {

        $s = $this->pdo->prepare("select * from {$this->table} where id < :starting_before limit {$this->pageSize}");
        $s->bindValue(':starting_before', $startingBefore, PDO::PARAM_INT);
        $s->execute();
        $this->results = $s->fetchAll() ?: [];

        return $this->results;
    }

    public function getPreviousLink () {
        return !$this->results ? '' : '?starting_before=' . $this->results[0]->id;
    }

    public function getNextLink () {
        if (!$this->results || count($this->results) < $this->pageSize) return '';

        return '?starting_after=' . $this->results[count($this->results)]->id;
    }
}

And the Pageable example

<?php

class PdoPageableTable implements Pageable {
    
    private $pdo;
    private $table;
    private $pageSize;
    private $collectionSize;
    
    public function __construct (PDO $pdo, $table, $pageSize = 100) {
        
        $this->pdo      = $pdo;
        $this->table    = $table;
        $this->pageSize = $pageSize;
    }
    
    public function getCollectionSize () {
        
        if ($this->collectionSize === null) {
            $s = $this->pdo->prepare("select count(id) from {$this->table}");
            $s->execute();
            $this->collectionSize = $s->fetchColumn('0');
        }
        
        return $this->collectionSize;
    }
    
    public function getPageSize () {
        return $this->pageSize;
    }
    
    
    public function getPagesCount () {
        return ceil($this->collectionSize / $this->getPageSize());
    }
    
    
    public function getPageLinks () {
        $pages = [];
        
        foreach (range(1, $this->getPagesCount()) as $page) {
            $pages[] = '?page=' . $page;
        }
        
        return $pages;
    }
}

The test file

<?php

$pagination = new PdoCursorAbleTable($pdo, 'carousel_image', 2);

echo "<pre>";

$startingAfter = 0;
$results = $pagination->fetchNext($startingAfter);

foreach ($results as $result) {
    // do something
}

echo $pagination->getNextLink();

echo "</pre>";