How to get nth page when querying documents?

[ryansumma]

I created an index to query 25 account documents from my accounts collection and display them on a table. There is a forward and back button to paginate 25 more account documents to display on the table using the code below.

Map(
    Paginate(Match(Index("most_recent_accounts"), Ref(Collection("organizations"), organization)), {
        size: 25,
        after: Ref(Collection("accounts"), "<id>")
    }),
    Lambda(
        "ref",
        Let(
            {
                accountDoc: Get(Var("ref"))
            },
            {
                id: Select(["ref", "id"], Var("accountDoc")),
                data: Select(["ref", "data"], Var("accountDoc"))
            }
        )
    )
);

If I have 200 account documents, that means there are 8 pages the user can navigate through. I’d like to display pages 1, 2, 3 … 8 below the table to allow the user to select which page to navigate to which would query that page of account documents and display them on the table. When the user clicks on page 3, how can I query the third page of account documents? (account documents 75 - 100).

[ptpaterson]

There is a direct answer to your question, and also a recommendation not to paginate your data like this. Fauna’s cursor-based pagination is efficient, but can be a different mental model than what you might be used to seeing (e.g. those page numbers at the bottom of a list). Let me answer your question first, though!

The solution here might help you: What is the best practice for offset pagination using FaunaDB? - #2 by databrecht

@databrecht

Let(
  {
    query:  Documents(Collection('users')),
    page: Paginate(
      Var('query') , 
      { size: 3, after: ..., before: ...} 
      // after or before won't be added on your first call of course
    ),
    count: Count(Var('query'))
  }, 
  {
    page: Var('page'),
    count: Var('count')
  }
)

You know the page size, so given the total count, you should be able to calculate the total number of pages that exist.

Limitations

Note that the Count function is limited to iterating over 100000 items in a Set. If you have more than 100000 Documents in a Collection, this will not work (See below for using Reverse as an alternative). Here’s a warning from the Docs:

The run time of Count is dependent on the number of elements in the underlying set or page — it’s linear, or O(n) . For very large sets or pages, executing Count might result in a query timeout error, or “width” error.

Performance cost

This also doesn’t help you jump to a future page. In order to do that, you need to paginate the entire set and then Drop what you don’t need. This performance limitation is not unique to Fauna, it’s how an SQL-based database would make such a jump – SQL just makes it easier to use such a big foot-gun. As an example, there is nothing magical about the SQL query

SELECT * FROM accounts LIMIT 25 OFFSET 900000

The database still needs to scan through the Table to skip over the right number of documents. The FQL equivalent would be something like

Map(
  Drop(900000, 
    Paginate(
      Documents(Collection("accounts")), 
      { size: 900025 },
    )
  ),
  Lambda("ref", Get(Var("ref")))
)

Either way, SQL or FQL, performance is bad.

Alternative: Leverage Fauna’s native keyset pagination

That first topic discussion I linked to contains some other links I recommend checking out as well.

Recommended way of doing a page offset?

This is easily translatable into fauna (assuming $LIMIT < 100k): Drop(Paginate(....., {size: $LIMIT}), $OFFSET). “But, Ben!” I hear you exclaim: “isn’t that really inefficient?”. Yes it is, and so is the SQL. What you probably want to do is keyset pagination, which is efficient, and also representable in fauna using the Range function, or using the before and after cursors.

The conclusions in these previous topics, and the articles they link to, is that you should be REALLY sure that you actually need your app to perform offset-pagination.

Instead, consider the “keyset” pagination that is proposed. In fact, if you are using the Paginate function with Fauna, you are already implementing keyset pagination, and it behooves you to think in of pagination in this way.

Example

Your example indicates that your users need to be able to access the last page of Documents. The way to do this natively with Fauna is to reverse the Index.

Paginate(
  Reverse(
    Match(Index("most_recent_accounts"), Ref(Collection("organizations"), organization))
  ), 
  { size: 25, after: ... }
  // the after cursor given will reflect paginating in reverse.
)