Closed byhow closed 2 weeks ago
WITH article_positions AS (
SELECT
ca.article_id,
ROW_NUMBER() OVER (ORDER BY ca."order") AS position
FROM
collection_article ca
WHERE
ca.collection_id = $1
),
total_count AS (
SELECT
COUNT(*) AS total,
CEIL(COUNT(*)::float / $2::float) AS total_pages
FROM collection_article
WHERE collection_id = $1
)
SELECT
ap.article_id AS id,
ap.position,
tc.total,
tc.total_pages,
CEIL(ap.position::float / $2::float) AS page_number
FROM article_positions ap
CROSS JOIN total_count tc
WHERE ap.article_id = $3;
$1 = collection_id $2 = per_page $3 = article_id
Description
See how bidirectional pagination works in Postgres
References
Story