Relay Cursor Spec Pagination in SQL

Relay Cursor Spec Pagination in SQL

-- --------------------- CREATE TABLE -------------------
-- Table: public.pagination

CREATE TABLE IF NOT EXISTS public.pagination
(
    uuid uuid NOT NULL DEFAULT gen_random_uuid(),
    ts timestamp without time zone NOT NULL DEFAULT now(),
    -- any columns
    data jsonb NOT NULL,
    
    CONSTRAINT _id PRIMARY KEY (uuid)
)

-- --------------------- INSERT TABLE -------------------
INSERT INTO public.pagination(data) VALUES ('{"baz": "foo"}');

-- --------------------- QUERY TABLE -------------------

WITH allresults AS (
   SELECT *
   FROM   pagination
   -- WHERE // you're WHERE clauses go here!
   )
SELECT *, count(*) OVER() AS full_count
FROM (
	SELECT * FROM allresults
	WHERE uuid > '0ebc3d63-fc08-48e8-8c68-1eb077b7e2d4' AND uuid <= 'd5b4d321-3ab5-4e65-9664-764527ca95a4'
	ORDER BY uuid DESC -- // swap around to ASC depending on first/last being set
	LIMIT 4 -- // last or first
) as x
JOIN (SELECT count(*) FROM allresults) c(total) ON true
JOIN (SELECT count(*) FROM allresults WHERE uuid <= '0ebc3d63-fc08-48e8-8c68-1eb077b7e2d4') d(before) ON true -- cursor 'after'
JOIN (SELECT count(*) FROM allresults WHERE uuid > 'd5b4d321-3ab5-4e65-9664-764527ca95a4' ) e(after) ON true -- cursor 'before'
ORDER BY uuid ASC -- DESC -- // swap around to ASC depending on first/last being set
LIMIT 2 -- // first or first

-- --------------------- ALT QUERY -------------------

WITH allresults AS (
   SELECT *
   FROM   pagination
   -- WHERE
   )
SELECT *, count(*) OVER() AS full_count
FROM (
	SELECT * FROM allresults
	WHERE uuid > '0ebc3d63-fc08-48e8-8c68-1eb077b7e2d4' AND uuid <= 'd5b4d321-3ab5-4e65-9664-764527ca95a4'
	ORDER BY ts DESC
	LIMIT 4
) as x
JOIN (SELECT count(*) FROM allresults) c(total) ON true
JOIN (SELECT count(*) FROM allresults WHERE uuid <= '0ebc3d63-fc08-48e8-8c68-1eb077b7e2d4') d(before) ON true
JOIN (SELECT count(*) FROM allresults WHERE uuid > 'd5b4d321-3ab5-4e65-9664-764527ca95a4' ) e(after) ON true
ORDER BY ts ASC -- DESC
LIMIT 2