Keyset/seek pagination and filter by search term

Question

Using table products(id, title, price, category_id)

How to filter product by title, price, category_id, pieces and retrieve second page using keyset/seek pagination? a page have 10 items

https://blog.jooq.org/2016/08/10/why-most-programmers-get-pagination-wrong/

SQL using offset pagination will be

SELECT * FROM products
WHERE title like '%search_term%' AND price > 100 AND price < 400 AND category_id=11
ORDER BY price DESC
LIMIT 10 OFFSET 10

Results can be in this order

(id=23, title='Some text', price=354, category_id=11)
(id=41, title='Big text', price=333, category_id=11)
(id=43, title='big big text', price=333, category_id=11)
(id=38, title='A text', price=288, category_id=11)
(id=11, title='text', price=200, category_id=11)

Answer 1

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 = 11
ORDER BY price DESC, id DESC
LIMIT 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 DESC
LIMIT 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 <= 200
AND (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();