Last active
October 28, 2021 19:26
-
-
Save AndrewIngram/821d5d5dcb498f2617d89b72c4a36445 to your computer and use it in GitHub Desktop.
Batch pagination
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- This is one technique for batching up the *first* page of results when doing pagination. | |
-- Potentially useful when using GraphQL and Dataloader. | |
-- | |
-- In this example, we return the first 10 books whose author_id is 1,2,3,4 or 5 and genre | |
-- is "biography", ordered by "title" | |
-- | |
-- For cursor-based (keyset) pagination, this technique won't work for anything after the | |
-- first "page", because the where clause needs to be the same for every entry in the | |
-- batch, which means you can't use different cursors. In practice, there isn't usually a | |
-- need to batch up subsequent pages, because your "next page" GQL queries will typically | |
-- only operate over one connection at a time. | |
SELECT | |
* | |
FROM ( | |
SELECT | |
ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY title) AS r, | |
t.* as t | |
FROM | |
books t | |
WHERE | |
author_id in (1,2,3,4,5) | |
AND | |
genre = 'biography' | |
) x | |
WHERE | |
x.r <= 10; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Updated fiddle with 1000 rows and both approaches
https://www.db-fiddle.com/f/vjFjXod1UeznuFpP5jyQAY/2