PostgreSQL: How to create cursor based pagination with sorting of aggregation results and id?

Question

I want to create a cursor based pagination feature for GraphQL. Below is the data without pagination.

SELECT 
    "Post"."id",
    COALESCE(SUM("PostVote"."value"), 0)::int as "voteScore"
FROM "Post"
LEFT JOIN "PostVote" ON "PostVote"."postId" = "Post"."id"
WHERE "Post"."questionId" = 28
GROUP BY "Post"."id"
ORDER BY "voteScore" DESC, id ASC

id	voteScore
32	2
30	0
31	0
33	0
34	0
29	-1

When I try to implement cursor based pagination with LIMIT 2 and id (cursor) after 30, it produces incorrect results.

SELECT 
    "Post"."id",
    COALESCE(SUM("PostVote"."value"), 0)::int as "voteScore"
FROM "Post"
LEFT JOIN "PostVote" ON "PostVote"."postId" = "Post"."id"
WHERE "Post"."questionId" = 28
    AND "Post"."id" > 30 -- new
GROUP BY "Post"."id"
ORDER BY "voteScore" DESC, id ASC
LIMIT 2 -- new

id	voteScore
32	2
31	0

My expected results are:

id	voteScore
31	0
33	0

How do you maintain the order without pagination like the first table above? The query I created seems to only care about the id, not caring about the aggregated data, voteScore. Basically I want to sort them by "voteScore".

Answer 1

You can't filter unconditionally on id >30, because then you will miss the row for 29 when the paging gets that far. So you need to apply the id filter only within ties of the aggregate:

SELECT
    "Post"."id",
    COALESCE(SUM("PostVote"."value"), 0)::int as "voteScore"
FROM "Post"
LEFT JOIN "PostVote" ON "PostVote"."postId" = "Post"."id"
WHERE "Post"."questionId" = 28
GROUP BY "Post"."id"  
HAVING COALESCE(SUM("PostVote"."value"), 0)::int <0 or COALESCE(SUM("PostVote"."value"), 0)::int =0 and id >30
ORDER BY "voteScore" DESC, id ASC
LIMIT 2

You could make this slightly cleaner by using a tuple comparison, but since they are sorted in different directions that won't work without some trickery.

SELECT
    "Post"."id",
    COALESCE(SUM("PostVote"."value"), 0)::int as "voteScore"
FROM "Post"
LEFT JOIN "PostVote" ON "PostVote"."postId" = "Post"."id"
WHERE "Post"."questionId" = 28
GROUP BY "Post"."id"
HAVING (COALESCE(SUM("PostVote"."value"), 0)::int,-id)  < (0,-30)
ORDER BY "voteScore" DESC, -id desc
LIMIT 2;