Paginating Database Results
When developing web applications, you often meet with the requirement to print out a restricted number of records on a page.
We come out of the state when we list all the data without paging. To select data from the database, we have the
ArticleRepository class, which contains the constructor and the findPublishedArticles method, which returns all
published articles sorted in descending order of publication date.
namespace App\Model;
use Nette;
class ArticleRepository
{
public function __construct(
private Nette\Database\Connection $database,
) {
}
public function findPublishedArticles(): Nette\Database\ResultSet
{
return $this->database->query('
SELECT * FROM articles
WHERE created_at < ?
ORDER BY created_at DESC',
new \DateTime,
);
}
}
In the Presenter we then inject the model class and in the render method we will ask for the published articles that we pass to the template:
namespace App\UI\Home;
use Nette;
use App\Model\ArticleRepository;
class HomePresenter extends Nette\Application\UI\Presenter
{
public function __construct(
private ArticleRepository $articleRepository,
) {
}
public function renderDefault(): void
{
$this->template->articles = $this->articleRepository->findPublishedArticles();
}
}
The default.latte template will then take care of listing the articles:
{block content}
<h1>Articles</h1>
<div class="articles">
{foreach $articles as $article}
<h2>{$article->title}</h2>
<p>{$article->content}</p>
{/foreach}
</div>
In this way, we can write all articles, but this will cause problems when the number of articles grows. At that point, it will be useful to implement the paging mechanism.
This will ensure that all articles are split into several pages and we will only show the articles of one current page. The total number of pages and the distribution of the articles is calculated by Paginator itself, depending on how many articles we have in total and how many articles we want to display on the page.
In the first step, we will modify the method for getting articles in the repository class to return only single-page articles. We will also add a new method to get the total number of articles in the database, which we will need to set a Paginator:
namespace App\Model;
use Nette;
class ArticleRepository
{
public function __construct(
private Nette\Database\Connection $database,
) {
}
public function findPublishedArticles(int $limit, int $offset): Nette\Database\ResultSet
{
return $this->database->query('
SELECT * FROM articles
WHERE created_at < ?
ORDER BY created_at DESC
LIMIT ?
OFFSET ?',
new \DateTime, $limit, $offset,
);
}
/**
* Returns the total number of published articles
*/
public function getPublishedArticlesCount(): int
{
return $this->database->fetchField('SELECT COUNT(*) FROM articles WHERE created_at < ?', new \DateTime);
}
}
The next step is to edit the presenter. We will forward the number of the currently displayed page to the render method. In the case that this number is not part of the URL, we need to set the default value to the first page.
We also expand the render method to get the Paginator instance, setting it up, and selecting the correct articles to display in the template. HomePresenter will look like this:
namespace App\UI\Home;
use Nette;
use App\Model\ArticleRepository;
class HomePresenter extends Nette\Application\UI\Presenter
{
public function __construct(
private ArticleRepository $articleRepository,
) {
}
public function renderDefault(int $page = 1): void
{
// We'll find the total number of published articles
$articlesCount = $this->articleRepository->getPublishedArticlesCount();
// We'll make the Paginator instance and set it up
$paginator = new Nette\Utils\Paginator;
$paginator->setItemCount($articlesCount); // total articles count
$paginator->setItemsPerPage(10); // items per page
$paginator->setPage($page); // actual page number
// We'll find a limited set of articles from the database based on Paginator's calculations
$articles = $this->articleRepository->findPublishedArticles($paginator->getLength(), $paginator->getOffset());
// which we pass to the template
$this->template->articles = $articles;
// and also Paginator itself to display paging options
$this->template->paginator = $paginator;
}
}
The template already iterates over articles in one page, just add paging links:
{block content}
<h1>Articles</h1>
<div class="articles">
{foreach $articles as $article}
<h2>{$article->title}</h2>
<p>{$article->content}</p>
{/foreach}
</div>
<div class="pagination">
{if !$paginator->isFirst()}
<a n:href="default, 1">First</a>
|
<a n:href="default, $paginator->page-1">Previous</a>
|
{/if}
Page {$paginator->getPage()} of {$paginator->getPageCount()}
{if !$paginator->isLast()}
|
<a n:href="default, $paginator->getPage() + 1">Next</a>
|
<a n:href="default, $paginator->getPageCount()">Last</a>
{/if}
</div>
This is how we've added pagination using Paginator. If Nette Database
Explorer is used instead of Nette Database Core as a database
layer, we are able to implement paging even without Paginator. The Nette\Database\Table\Selection class contains the
page method with pagination logic taken
from the Paginator.
The repository will look like this:
namespace App\Model;
use Nette;
class ArticleRepository
{
public function __construct(
private Nette\Database\Explorer $database,
) {
}
public function findPublishedArticles(): Nette\Database\Table\Selection
{
return $this->database->table('articles')
->where('created_at < ', new \DateTime)
->order('created_at DESC');
}
}
We do not have to create Paginator in the Presenter, instead we will use the method of Selection object returned
by the repository:
namespace App\UI\Home;
use Nette;
use App\Model\ArticleRepository;
class HomePresenter extends Nette\Application\UI\Presenter
{
public function __construct(
private ArticleRepository $articleRepository,
) {
}
public function renderDefault(int $page = 1): void
{
// We'll find published articles
$articles = $this->articleRepository->findPublishedArticles();
// and their part limited by page method calculation we'll pass to the template
$lastPage = 0;
$this->template->articles = $articles->page($page, 10, $lastPage);
// and the necessary data to display paging options as well
$this->template->page = $page;
$this->template->lastPage = $lastPage;
}
}
Because we do not use Paginator, we need to edit the section showing the paging links:
{block content}
<h1>Articles</h1>
<div class="articles">
{foreach $articles as $article}
<h2>{$article->title}</h2>
<p>{$article->content}</p>
{/foreach}
</div>
<div class="pagination">
{if $page > 1}
<a n:href="default, 1">First</a>
|
<a n:href="default, $page - 1">Previous</a>
|
{/if}
Page {$page} of {$lastPage}
{if $page < $lastPage}
|
<a n:href="default, $page + 1">Next</a>
|
<a n:href="default, $lastPage">Last</a>
{/if}
</div>
In this way, we implemented a paging mechanism without using a Paginator.
