Pagination helper class

Question

The most nauseous problem with pagination is the fact that you have to write two almost identical queries. For the various reasons I am not inclined to use the SQL-only way (such as select count(*) from (original query goes here)). Hence I am trying to modify the original query. Being aware that this method is not fully reliable, I am doing this in a semi-automatic way, offering the automated method for the regular queries with the occasional fallback to setting the count query automatically.

The goal was to make the application code simple and at the same time explicit. The full compatibility with PDO's fetchAll() was a requirement.

The code works perfectly for me so far, but obviously I may have overlooked some edge cases. Also I am not sure about the code distribution between methods.

Here is the code

class Pagination
{
 protected PDO $pdo;
 protected string $sql;
 protected array $params;
 protected string $countSql;
 protected int $limit = 10;
 public function __construct(PDO $pdo, string $sql, array $params = []) {
 $this->pdo = $pdo;
 $this->sql = $sql;
 $this->params = $params;
 }
 public function setCountQuery(string $sql) {
 $this->countSql = $sql;
 return $this;
 }
 public function setLimit(int $limit) {
 $this->limit = $limit;
 return $this;
 }
 
 public function getPageCount():int {
 return (int)ceil($this->getNumRecords() / $this->limit);
 }
 
 public function getNumRecords() {
 $this->countSql = $this->countSql ?? $this->getAutoCountQuery();
 $stmt = $this->pdo->prepare($this->countSql);
 $stmt->execute($this->params);
 return $stmt->fetchColumn();
 }
 
 public function getPageData(int $page, $mode = null, ...$fetchAllParams) {
 $offset = ($page - 1) * $this->limit;
 $limit = (int)$this->limit;
 $mode = $mode ?? $this->pdo->getAttribute(PDO::ATTR_DEFAULT_FETCH_MODE);
 
 $sql = "$this->sql LIMIT $offset, $limit";
 $stmt = $this->pdo->prepare($sql);
 $stmt->execute($this->params);
 return $stmt->fetchAll($mode, ...$fetchAllParams);
 }
 public function getAutoCountQuery() {
 $pat = '~^(select)(.*)(\s+from\s+)~i';
 return preg_replace($pat, '1ドル count(*)3ドル', $this->sql);
 }
}

And here is a simple testing snippet, assuming you have a working PDO instance

$pdo->query("create temporary table test(i int)");
$pdo->query("insert into test (i) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)");
$pagination = new Pagination($pdo, "select * from test");
$pagination->setLimit(5);
#$pagination->setCountQuery("select count(*) from test");
$pageCount = $pagination->getPageCount();
$data = $pagination->getPageData(1);
$data2 = $pagination->getPageData(2, PDO::FETCH_COLUMN);
$data3 = $pagination->getPageData(3, PDO::FETCH_CLASS, 'stdClass');
var_dump($pageCount, json_encode($data), json_encode($data2), json_encode($data3));

• mickmackusa: If someone ever feeds in a multi-line sql string, then the dot in the regex might not work as expected. I might suggest preg_replace('~^select\K.*?(?=\s+from\s)~is', ' COUNT(*)', $this->sql) Is it too soon to expect the null coalescing assignment operator?
• Dharman: Your methods are missing return type hints. Even if you are still on PHP 7.4 I would add them. If that's not possible then at least docblock comments.

Answer 1

Someone using your class might not be aware that every time the getPageCount() method is used a SQL query is executed. Ideally it should only be executed once. This can be achieved with code like this:

class Pagination
{
 protected int $numRecords = -1;
 public function getNumRecords() {
 if ($this->numRecords < 0) {
 $this->countSql = $this->countSql ?? $this->getAutoCountQuery();
 $stmt = $this->pdo->prepare($this->countSql);
 $stmt->execute($this->params);
 $this->numRecords = $stmt->fetchColumn();
 }
 return $this->numRecords;
 } 
}

I left out all the properties and methods irrelevant to this example.

Question: How can you bind PHP parameters to your queries?