Keyset/seek pagination and filter by search term

A pure SQL solution for keyset pagination

The first thing to understand when talking about keyset pagination is that there's no such thing as the "second" page. There are only "next pages" given a "current page". In your case, the current page would be the one that ends on:

(id=11, title='text', price=200, category_id=11)

So, the next page would be the one that has a (price, id) < (200, 11) (current price, id). If this query produces your first page:

SELECT * FROM products-- "Ordinary predicates"WHERE title LIKE '%search_term%' AND price > 100 AND price < 400 AND category_id = 11ORDER BY price DESC, id DESCLIMIT 10

Then, this query will produce your next page

SELECT * FROM products-- "Ordinary predicates"WHERE title LIKE '%search_term%' AND price > 100 AND price < 400 AND category_id = 11-- "Keyset pagination predicate"AND (price, id) < (200, 11)ORDER BY price DESC, id DESCLIMIT 10

Alternatively, that predicate could be expanded to this:

-- "Keyset pagination predicates"AND (price < 200 OR price = 200 AND id < 11)

Or even to this:

-- "Keyset pagination predicate"AND price <= 200AND (price < 200 OR price = 200 AND id < 11)

Depending on the database, the three different predicates may perform differently

A jOOQ solution

Since you're referencing the jOOQ blog, here's how you would write the query on the second page using jOOQ:

DSL.using(configuration)   .selectFrom(PRODUCTS)   .where(PRODUCTS.TITLE.like("%search_term%")   .and(PRODUCTS.PRICE.gt(100))   .and(PRODUCTS.PRICE.lt(400))   .and(PRODUCTS.CATEGORY_ID.eq(11))   .orderBy(PRODUCTS.PRICE.desc(), PRODUCTS.ID.desc())   .seek(200, 11) // Automatic generation of keyset pagination predicates   .limit(10)   .fetch();