Slide 33
Slide 33 text
WITH _SOURCE AS ($table),
_T AS (
SELECT ROW_NUMBER() OVER (ORDER BY ${orderBy}) AS n, ${idColumn} AS id
FROM _SOURCE
ORDER BY ${orderBy}
),
_afterN AS (SELECT n FROM _T WHERE _T.id = ${after}),
_beforeN AS (SELECT n FROM _T WHERE _T.id = ${before}),
_totalN AS (SELECT COUNT(1) AS total FROM _T),
_page AS (
SELECT n, _T.id
FROM _T
WHERE (${cursor.after} IS NULL OR n > COALESCE((TABLE _afterN), 0))
AND (${cursor.first} IS NULL OR n <= COALESCE((TABLE _afterN), 0) + ${cursor.first})
AND (${cursor.before} IS NULL OR n < COALESCE((TABLE _beforeN), (TABLE _totalN) + 1))
AND (${cursor.last} IS NULL OR n >= COALESCE((TABLE _beforeN), (TABLE _totalN) + 1) - ${cursor.last})
),
_minN AS (SELECT MIN(n) FROM _page),
_maxN AS (SELECT MAX(n) FROM _page)
SELECT
_totalN.total,
CASE WHEN (TABLE _minN) > 1 THEN true ELSE false END AS has_previous_page,
CASE WHEN (TABLE _maxN) < (TABLE _totalN) THEN true ELSE false END AS has_next_page,
_page.id
FROM _page, _totalN
Pagination SQL (!)