Paginating Database Results
***************************

.[perex]
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.

```php
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:

```php
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:

```latte
{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 [utils: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:

```php
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:

```php
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:

```latte
{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>
		&nbsp;|&nbsp;
		<a n:href="default, $paginator->page-1">Previous</a>
		&nbsp;|&nbsp;
	{/if}

	Page {$paginator->getPage()} of {$paginator->getPageCount()}

	{if !$paginator->isLast()}
		&nbsp;|&nbsp;
		<a n:href="default, $paginator->getPage() + 1">Next</a>
		&nbsp;|&nbsp;
		<a n:href="default, $paginator->getPageCount()">Last</a>
	{/if}
</div>
```


This is how we've added pagination using Paginator. If [Nette Database Explorer |database:explorer] is used instead of [Nette Database Core |database:core] as a database layer, we are able to implement paging even without Paginator. The `Nette\Database\Table\Selection` class contains the [page |api:Nette\Database\Table\Selection::_ page] method with pagination logic taken from the Paginator.

The repository will look like this:

```php
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:

```php
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:

```latte
{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>
		&nbsp;|&nbsp;
		<a n:href="default, $page - 1">Previous</a>
		&nbsp;|&nbsp;
	{/if}

	Page {$page} of {$lastPage}

	{if $page < $lastPage}
		&nbsp;|&nbsp;
		<a n:href="default, $page + 1">Next</a>
		&nbsp;|&nbsp;
		<a n:href="default, $lastPage">Last</a>
	{/if}
</div>
```

In this way, we implemented a paging mechanism without using a Paginator.

{{priority: -1}}
{{sitename: Best Practices}}