Drupal: Paging an SQL query

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:

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 which extends the SelectInterface (meaning it decorates / proxies the object, see the ExtendableInterface). It then utilizes the PagerManager to manage the paging, adding the current page and limit as a range to the Select before it’s executed, see the PagerSelectExtender::execute() function.

The snippet uses the same Pager object as the query to get the total items, and supplies the Pager 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 extends SelectExtender, which implements SelectInterface 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.

/** @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 implementing the SelectInterface, so why not simply chain the whole query like so:

/** @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 which is what the ExtendableInterface::extend() function returns. Meanwhile the actual type would be the PagerSelectExtender(99.9% of the time anyway, it can change depending on the Select class implementation and driver class overrides).

What about `Drupal::entityQuery()`?

The QueryInterface (which is what \Drupal::entityQuery() returns) has a pager($limit = 10, $element = NULL), 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

$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

/** @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 ~~tiny~~ pedantic thing to notice is that in this example I use \Drupal::service('pager.manager') instead of \Drupal::database()->getPagerManager() to get the PagerManager. 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 function calls the ::service, while the PagerSelectExtender uses the getPagerManager() in its execute() function.
This is all a big fuss over nothing, but in theory the getPagerManager() function on the Connection 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.