jimmyhuang

How to do pagination in a nested graphql query with dataloader batch load?

I have a GraphQL server implemented with absinthe, dataloader and phoenix.

A sample query is as follows
What I anticipate is that the query lists the first 10 books in database and for each book, the first 10 chapters of the book.

{
  listBook(from:0, size:10){
    books {
      id
      name
      url
      chapters(from: 0, size: 10) {
        id
        name
        book{
          id
          name
          book {
            id
          }
        }
      }
    }
    total
  }
}

I am using dataloader to load chapters field.

However, the generated sql is not expected, instead of returning first 10 chapters for each book, the underlying sql actually returns first 10 chapters of all chapters of the first 10 books.

How can I achieve my goal? Make my GraphQL API return first 10 chapters of each book.

[debug] QUERY OK source="books" db=60.4ms
SELECT b0."id", b0."html", b0."info", b0."name", b0."preface", b0."text", b0."url", b0."info_html", b0."preface_html", b0."parent_id", b0."inserted_at", b0."updated_at", b0."parent_id" FROM "books" AS b0 WHERE (b0."parent_id" = ANY($1)) ORDER BY b0."parent_id" LIMIT $2 OFFSET $3 [[291289, 291066, 291009, 290794, 290647, 290409, 290196, 157109, 155130, 153693], 10, 0]

If I do not use dataloader but use a custom resolver, I can achieve my goal but that results in 1+10 queries because for each book the application needs to query database for its chapters.

Is there a way to use dataloader to batch load chapters?

The complete source code is here GitHub - huangjimmy/wikisource: A wikisource mirror site in Elixir and Phonenix · GitHub

2 comments

#absinthe #dataloader

0 3467 2

2019-09-10 11:09:56 UTC

First Post!

maartenvanvliet

It’s a tough problem but should be doable with dataloader and sql window functions. I’ve been meaning to take a crack at it but haven’t come around to it.

Post #1

Most Liked

jimmyhuang

I come up with a solution and it does work. I do use PostgreSQL window function. The solution will only work with PostgreSQL.

1 I add a virtual field chapter_number

lib/wikisource/book.ex

schema "books" do
    field :chapter_number, :integer, virtual: true

2 I inject a chapters: true parameter so that datasource.ex knows a query is a chapter query

lib/wikisource_web/graphql/schema/types.ex

    field :chapters, list_of(:book) do
      arg(:offset, non_null(:integer))
      arg(:first, non_null(:integer))
      resolve(dataloader(DataSource, :chapters, args: %{chapters: true}))

3 I construct the query with window function

I only select a subset of fields because some field is too large and costly to load.

lib/wikisource_web/graphql/datasource.ex

  def query(queryable, params) do
   case params do
      %{chapters: true, offset: offset, first: first} ->
        last = offset + first
        query = from r in queryable, select: r, select_merge: %{chapter_number: fragment("row_number() over (PARTITION by parent_id order by \"name\")")}
        from r in subquery(query), select: %Wikisource.Book{id: r.id, name: r.name, info: r.info, preface: r.preface, info_html: r.info_html, preface_html: r.preface_html}, where: r.chapter_number >= ^offset and r.chapter_number < ^last
      %{order_by: order_by, offset: from, first: size} -> from record in queryable, order_by: ^order_by, offset: ^from, limit: ^size

Post #2