Skip to main content

Keyset Seek Method

If more than one column is involved in the sorting, there are two ways to construct the WHERE clause in keyset pagination: using SQL row values, and using an approximated variant.

The Approximated Variant​

SELECT *
FROM post p
WHERE p.date >= :date
   AND NOT (p.date = :date AND p.title <= :title)
   AND NOT (p.date = :date AND p.title = :title AND p.id <= :id)
ORDER BY p.date ASC, p.title ASC, p.id ASC

This method is slightly less efficient than using SQL row values, but it is more portable across different database systems. It just works in all cases. This is the default seek method used by the library.

SQL Row Values​

SELECT *
FROM post p
WHERE (p.date, p.title, p.id) > (:date, :title, :id)
ORDER BY p.date ASC, p.title ASC, p.id ASC

This method might be slightly more efficient than the approximated variant, but the support is not uniform across different database systems.

It also requires that all the columns are ordered in the same direction. For example, if the first column is ordered in ascending order, the rest of the columns must also be ordered in ascending order. If your requirement is to have a different order for each column, you have to use the approximated variant.

Adapter Support​

All the adapters that support keyset pagination support the approximated variant. The SQL row values method is supported by these adapters:

  • Doctrine ORM QueryBuilderAdapter
  • Doctrine ORM NativeQueryAdapter
  • Doctrine DBAL QueryBuilderAdapter adapter

In any case, the approximate variant is used by default for better interoperability and flexibility.

Changing the Seek Method​

To change the seek method, you can use the seekMethod argument. There are three options:

  • SeekMethod::Approximated
  • SeekMethod::RowValues
  • SeekMethod::Auto

SeekMethod::Auto means the adapter will use the row values method if all the sort columns are ordered in the same direction, otherwise it uses the approximated variant.

Example:

use Rekalogika\Rekapager\Keyset\KeysetPageable;
use Rekalogika\Rekapager\Adapter\Common\SeekMethod;
use Rekalogika\Rekapager\Doctrine\ORM\QueryBuilderAdapter;

$adapter = new QueryBuilderAdapter(
    queryBuilder: $queryBuilder,
    seekMethod: SeekMethod::RowValues,
);
note

To use SQL row values, Doctrine ORM QueryBuilderAdapter requires the REKAPAGER_ROW_VALUES DQL function to be registered. Read its documentation for more information.