# Drupal: Paging an SQL query

Every time I need to make a pager ind Drupal I need to ask AI or / and do some searching to figure out.  
I don’t know why I can’t remember how, I just can’t, so this is a boilerplate for me (and anyone else who find it useful) for making paged SQL queries.

## Snippet

The snippet below builds a simple render array that shows a paged list of all the node IDs:

```php
use Drupal\Core\Database\Query\PagerSelectExtender;

// ...

/** @var PagerSelectExtender */
$pager_query = \Drupal::database()->select('node', 'node')
  // The PagerSelectExtender decorates the Select object, and will
  // handle the connection between the PagerManager and the Select.
  ->extend(PagerSelectExtender::class);

// Manipulate the query so it matches the use case.
$pager_query
  ->fields('node', ['nid'])
  ->orderBy('node.nid')
  // Limit is a PagerSelectExtender function that sets items per page.
  ->limit(5);

$ids = $pager_query->execute()->fetchCol();

// Get the pager object holding information about the pager element.
$pager = \Drupal::database()->getPagerManager()->getPager($pager_query->getElement());

$build = [
  // Use the pager to get a summary with total items count.
  'summary' => ['#plain_text' => t('Count: @total', ['@total' => $pager->getTotalItems()]), '#suffix' => '<hr>'],
  // Print the content.
  'content' => ['#plain_text' => implode(', ', $ids), '#suffix' => '<hr>'],
  // Add the pager matching the pager element from the query.
  'pager' => ['#type' => 'pager', '#element' => $pager_query->getElement()],
];
```

The code should be self explanatory. I’ll quickly recap the inner workings of the [`PagerSelectExtender`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21PagerSelectExtender.php/class/PagerSelectExtender/11.x) which extends the [`SelectInterface`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21SelectInterface.php/interface/SelectInterface/11.x) (meaning it decorates / proxies the object, see the [`ExtendableInterface`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21ExtendableInterface.php/interface/ExtendableInterface/11.x)). It then utilizes the [`PagerManager`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Pager%21PagerManager.php/class/PagerManager/11.x) to manage the paging, adding the current page and limit as a range to the `Select` before it’s executed, see the [`PagerSelectExtender::execute()`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21PagerSelectExtender.php/function/PagerSelectExtender%3A%3Aexecute/11.x) function.

The snippet uses the same [`Pager`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Pager%21Pager.php/class/Pager/11.x) object as the query to get the total items, and supplies the [`Pager`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Render%21Element%21Pager.php/class/Pager/11.x) element with the same pager ID (`#element`) as the query, to allow for multiple pagers on the same page.

### When to extend the `Select`?

Since the [`PagerSelectExtender`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21PagerSelectExtender.php/class/PagerSelectExtender/11.x) extends [`SelectExtender`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21SelectExtender.php/class/SelectExtender/11.x), which implements [`SelectInterface`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21SelectInterface.php/interface/SelectInterface/11.x) it can extend the `Select` object anytime, and you can easily work with the new `PagerSelectExtender` object as if it was a regular `Select`.

I prefer to extend the `Select` object as part of the first chain, this avoids an intermediate `$query` variable and gives a clear intention of the query from the start.

```php
/** @var PagerSelectExtender */
$pager_query = \Drupal::database()->select('node', 'node')
  ->extend(PagerSelectExtender::class);
$pager_query->fields('node', ['nid'])->condition('node.type', 'article');

// vs

$query = \Drupal::database()->select('node', 'node')
  ->fields('node', ['nid'])->condition('node.type', 'article');
/** @var PagerSelectExtender */
$pager_query = $query->extend(PagerSelectExtender::class);
```

You could also reassign the query to it self: `$query = $query->extend(PagerSelectExtender::class);`, I’m not sure where I land on variable reassignment in general.  
But anything is possible, you should follow your code conventions or what you feel is the most maintainable code.

### It’s a simple query, why break the chain?

I made a thing about [`PagerSelectExtender`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21PagerSelectExtender.php/class/PagerSelectExtender/11.x) implementing the [`SelectInterface`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21SelectInterface.php/interface/SelectInterface/11.x), so why not simply chain the whole query like so:

```php
/** @var PagerSelectExtender */
$query = \Drupal::database()->select('node', 'node')
  ->extend(PagerSelectExtender::class)
  ->fields('node', ['nid'])
  ->orderBy('node.nid')
  ->limit(5);
```

This being a boilerplate I want it to be easy to build on when I copy paste it into something. Some of the functions in the `SelectInterface` are not being chainable (join functions come to mind), I like to have the space to quickly add these functions without having to split up the chain - It’s a small thing I know, but the easier something is to use the more I use it.

But more importantly my VSCode wont let me autocomplete anything beyond `extend()` and static code analysis wont know about the functions either.  
This is because you can’t type hint inside a chain. So after the `extend(PagerSelectExtender::class)` function, the hinted type will be [`ExtendableInterface`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21ExtendableInterface.php/interface/ExtendableInterface/11.x) which is what the [`ExtendableInterface::extend()`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21ExtendableInterface.php/function/ExtendableInterface%3A%3Aextend/11.x) function returns. Meanwhile the actual type would be the `PagerSelectExtender`(99.9% of the time anyway, it can change depending on the [`Select`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21Select.php/function/Select%3A%3Aextend/11.x) class implementation and [driver class overrides](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Connection.php/function/Connection%3A%3AgetDriverClass/11.x)).

## What about [`Drupal::entityQuery()`](https://api.drupal.org/api/drupal/core%21lib%21Drupal.php/function/Drupal%3A%3AentityQuery/11.x)?

The [`QueryInterface`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Entity%21Query%21QueryInterface.php/interface/QueryInterface/11.x) (which is what `\Drupal::entityQuery()` returns) has a [`pager($limit = 10, $element = NULL)`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Entity%21Query%21QueryInterface.php/function/QueryInterface%3A%3Apager/11.x), function so you’d think it would be easy, and it is, as long as you don’t need to support multiple pagers on the same page. Multiple pagers on the same page is a rare occurrence, but you’ll never know if there’s something else on the page that uses pagers.  
Now in the case you’re sure nothing else will ever happen on a page (this is usually one-off admin pages for me) let me start with the simplest implementation:

### Without multiple pagers

```php
$ids = \Drupal::entityQuery('node')
  ->accessCheck(FALSE)
  ->pager(5)->execute();

$build = [
  // Print the content.
  'content' => ['#plain_text' => implode(', ', $ids), '#suffix' => '<hr>'],
  // Add the pager matching the pager element from the query.
  'pager' => ['#type' => 'pager'],
];
```

### With multiple pagers

```php
/** @var Drupal\Core\Pager\PagerManagerInterface */
$pager_manager = \Drupal::service('pager.manager');

// Get the next available pager ID
$pager_id = $pager_manager->getMaxPagerElementId() + 1;

$ids = \Drupal::entityQuery('node')
  ->accessCheck(FALSE)
  // Supply the pager function with the next pager_id
  ->pager(2, $pager_id)
  ->execute();

// 
$pager = $pager_manager->getPager($pager_id);

$variables['page']['content'][] = [
  'summary' => ['#plain_text' => t('Count: @total', ['@total' => $pager->getTotalItems()]), '#suffix' => '<hr>'],
  'content' => ['#plain_text' => implode(', ', $ids), '#suffix' => '<hr>'],
  'pager' => ['#type' => 'pager'],
];
```

Much of the code is the same as the very first snippet, so there’s not much to go over.

One <s>tiny</s> pedantic thing to notice is that in this example I use [`\Drupal::service('pager.manager')`](https://api.drupal.org/api/drupal/core%21core.services.yml/service/pager.manager/10) instead of [`\Drupal::database()->getPagerManager()`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Connection.php/function/Connection%3A%3AgetPagerManager/10) to get the [`PagerManager`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Pager%21PagerManagerInterface.php/interface/PagerManagerInterface/10). Both these functions end up getting the exact same `PageManager` instance, the `getPagerManager()` function calls `\Drupal::service('pager.manager')`.  
The reason for using `::service` with when working with `entityQuery` is the [`QueryBase::pager`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Entity%21Query%21QueryBase.php/function/QueryBase%3A%3Apager/10) function calls the `::service`, while the [`PagerSelectExtender`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21PagerSelectExtender.php/class/PagerSelectExtender/10) uses the `getPagerManager()` in its [`execute()`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21PagerSelectExtender.php/function/PagerSelectExtender%3A%3Aexecute/10) function.  
This is all a big fuss over nothing, but in theory the `getPagerManager()` function on the [`Connection`](https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Connection.php/class/Connection/10) class could use something else.  
With all that being said, use whatever way of getting the `PagerManager` you’re comfortable with.

## The end

It seems like a lot of talk about a very simple solution, but sometimes it’s fun to dive into the little things, hope you enjoyed.