Graphql nested cursor based pagination, resolver and SQL query

Question

Is there a way to implement graphql cursor based pagination with nested pagination queries in a performant way?

Let's say we have 3 pseudo graphql types:

type User {
  id: ID!
  books: [Book!]!
}

type Book {
  isbn: ID!
  pages: [Page!]!
}

type Page {
  num: ID!
}

For simplicity let's say user could read thousands of books and each book can have hundred of pages.

user database table:

id
1
2
3
...etc

book database table:

isbn
1
2
3
...etc

page database table:

num
1
2
3
...etc

user_book database table:

user_id | book_isbn
1       | 2
1       | 3
2       | 1
...etc

book_page database table:

book_isbn | page_num
1         | 1
1         | 2
2         | 3
...etc

We can't load million users, thousands of books they read and hundreds of pages, so we do a pagination. Let's say we want to load 100 users, first 50 books that each of them read and first 10 pages of each book:

{
  users(first: 100, after: "9") {
   edges { 
     node {
       id
       books(first: 50) {
         edges {
           node {
             id
             pages(first: 10) {
               node {
                 id
               }
             }
           }
         }
       }
     }
   }
}

We can load users 10..110, then for each user books resolver use parent user id to load 50 books and for each book pages resolver load 10 pages:

// pseudo code
const resolvers = {
  // get users from 10 to 110
  users: (_, args) => `SELECT * FROM user WHERE id > args.after LIMIT 100`,
  User: {
    books: (root) => `SELECT * FROM book JOIN user_book ub WHERE ub.user_id = root.id LIMIT 50`
  },
  Book: {
    pages: (root) => `SELECT * FROM page JOIN book_page bp WHERE bp.book_isbn = root.isbn LIMIT 10`
  }
};

Problem 1 SQL

We do 1 database request to get 100 users, then 100 requests to get books for each user and finally 500 requests to get pages for each book (100 users * 50 books). Total of 601 database request for one query :(

If we didn't have pagination we could use dataloader to batch user ids into array in books resolver and book ids in pages resolver to do only 3 database requests. But how can having array of user ids 10...100 can we query only 50 books for each user and the same for pages?

Problem 2 Graphql

We can't use cursor for nested paginated queries (books and pages) because we don't know them and even if we did we could not pass them separately for each group:

{
  users(first: 100, after: "9") {
   edges { 
     node {
       id
       // this will not work because for each user books group we need it's own cursor
       books(first: 50, after: "99") {
         edges {
           node {
             id
           }
         }
       }
     }
   }
}

My only idea to solve this is to allow pagination only as top level query and never use it as field on type.

Benjamin M · Accepted Answer · 2022-06-15T16:13:38.147

Your first problem mostly depends on your database and its available queries. You basically want something like "select the top n books for each user". This can be done in SQL. See: Using LIMIT within GROUP BY to get N results per group?

The second problem feels a bit contrived in my opinion: On your initial page load, you fetch and display 100 users, and for each user 50 books, and for each book 10 pages.

Now think in terms of the UI: There will be a button for each displyed user to fetch the next 50 books for that user. Though there's no need for nested pagination. You can simply use the books query endpoint for that.

I don't see a common real world scenario where you want to "fetch the next 50 books for every displayed user".

The graphql schema would look something like this (I'll omit pages, because it is essentially the same as books):

Query {
  users(first: Int, after: UserCursor): UserConnection
  books(first: Int, after: BookCursor): BookConnection
}

type UserConnection {
  edges: [UserEdge]
}

type UserEdge {
  cursor: UserCursor
  node: User
}

type User {
  id: ID
  books(first: Int): BookConnection # no `after` argument here 
}

type BookConnection {
  edges: [BookEdge]
}

type BookEdge {
  cursor: BookCursor # here comes the interesting part
  node: Book
}

type Book {
  id: ID
}

The challenging part is: "What must BookEdge.cursor look like to make nested pagination work when you pass this cursor to Query.books?"

Simple answer: The cursor must additionally include the User.id, because your server code must be able to select the next 50 books for the given user id after the cursor, e.g. (simplified) SELECT * FROM book WHERE user = 42 LIMIT 50 OFFSET 50.

As you can see, this GraphQL schema does not have any nested pagination. It just has nested cursors. And those cursors don't care if they originate from the top level of the schema (like Query.users) or from some nested level (like Query.users.edges.node.books).

The only important part is, that the cursor must include all necessary information for your server code to fetch the correct data. In this case it might be something like cursor={Book.id, User.id}. The Book.id is needed to be able to fetch "the next 50 books after this id" and the User.id is needed to fetch "books from only this particular user".

Graphql nested cursor based pagination, resolver and SQL query

Problem 1 SQL

Problem 2 Graphql

1 Answers1