Postgres pagination research

[byhow]

Description

See how bidirectional pagination works in Postgres

References

• report thread

---

Story

• thematters/developer-resource#372

[byhow]

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