Pagination

Pagination refers to the process of splitting large result sets into smaller "pages". Doing so relieves the server, and the client, from dealing with potentially huge result sets in one step. The tradeoff is that more work needs to be done if you do need to process large result sets.

Currently, when you want to process all of the documents in a collection, for searching, sorting, or simply fetching one or more fields, an index is typically required (the only exception would be when you would track each document’s Ref independently, in your code).

This tutorial demonstrates several pagination scenarios:

This tutorial assumes that you have successfully prepared your database by creating the necessary collections and documents.

Basic pagination

The easiest way to demonstrate pagination is to use a "collection index", this is, an index that has no defined terms or values fields. Such indexes record each member document’s Ref. That makes collection indexes useful for retrieving all of the collection’s documents.

If you are using the Shell in the Cloud Console, the Console automatically created a collection index called all_letters. If you are using fauna-shell, you need to create the index. Copy the following query, paste it into the Shell, and run it:

CreateIndex({
  name: "all_letters",
  source: Collection("Letters")
})

There are several different ways that we can use collection indexes to fetch documents. For the following examples, copy a query and paste it into the Shell, and run it.

All documents, for small collections

Our Letters collection contains 26 documents, one for each letter in the alphabet. We can fetch them all in one query, using the index that we created in the previous section:

Paginate(Match(Index("all_letters")))

This query uses the Index function to specify that we’re working with the all_letters index. The Match function is used to "search" the index, producing a set of results; no search criteria is provided because the index has no terms specified with which to search. The Paginate function is used to traverse the result set’s entries.

When you run this query, the result should look similar to:

{
  "data": [
    Ref(Collection("Letters"), "101"),
    Ref(Collection("Letters"), "102"),
    Ref(Collection("Letters"), "103"),
    Ref(Collection("Letters"), "104"),
    Ref(Collection("Letters"), "105"),
    Ref(Collection("Letters"), "106"),
    Ref(Collection("Letters"), "107"),
    Ref(Collection("Letters"), "108"),
    Ref(Collection("Letters"), "109"),
    Ref(Collection("Letters"), "110"),
    Ref(Collection("Letters"), "111"),
    Ref(Collection("Letters"), "112"),
    Ref(Collection("Letters"), "113"),
    Ref(Collection("Letters"), "114"),
    Ref(Collection("Letters"), "115"),
    Ref(Collection("Letters"), "116"),
    Ref(Collection("Letters"), "117"),
    Ref(Collection("Letters"), "118"),
    Ref(Collection("Letters"), "119"),
    Ref(Collection("Letters"), "120"),
    Ref(Collection("Letters"), "121"),
    Ref(Collection("Letters"), "122"),
    Ref(Collection("Letters"), "123"),
    Ref(Collection("Letters"), "124"),
    Ref(Collection("Letters"), "125"),
    Ref(Collection("Letters"), "126")
  ]
}

The result demonstrates that the index contains references to the documents. These references are useful, we can pick one to fetch the details for a specific document.

Get(Ref(Collection("Letters"), "103"))

When that query is executed, the result should be similar to:

{
  "ref": Ref(Collection("Letters"), "103"),
  "ts": 1565299176485000,
  "data": {
    "letter": "C",
    "extra": "third"
  }
}

What if we wanted to see the contents of every document, and not just the refs? That’s where pagination is needed. We would change our Paginate query to look like this:

Map(
  Paginate(Match(Index("all_letters"))),
  Lambda("X", Get(Var("X")))
)

The points of interest for this query:

  • We use the Map function to process each of the Paginate results with a Lambda function.

  • We use the variable named X, just to provide a variable name via Var. You can use any variable name you like.

  • We only need one variable for the Lambda function, because our index only returns one value for each document in the collection: the document’s Ref. If our index specified a values field, we would need to use one variable per field defined by the values field.

  • The Lambda function simply calls the Get function to retrieve the document specified by the X variable.

When you run this query, the result should be similar to:

{
  "data": [
    {
      "ref": Ref(Collection("Letters"), "101"),
      "ts": 1565299176485000,
      "data": {
        "letter": "A",
        "extra": "First"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "102"),
      "ts": 1565299176485000,
      "data": {
        "letter": "B",
        "extra": "second"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "103"),
      "ts": 1565299176485000,
      "data": {
        "letter": "C",
        "extra": "third"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "104"),
      "ts": 1565299176485000,
      "data": {
        "letter": "D",
        "extra": "4th"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "105"),
      "ts": 1565299176485000,
      "data": {
        "letter": "E",
        "extra": "fifth"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "106"),
      "ts": 1565299176485000,
      "data": {
        "letter": "F",
        "extra": "sixth"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "107"),
      "ts": 1565299176485000,
      "data": {
        "letter": "G",
        "extra": "seventh"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "108"),
      "ts": 1565299176485000,
      "data": {
        "letter": "H",
        "extra": "eighth"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "109"),
      "ts": 1565299176485000,
      "data": {
        "letter": "I",
        "extra": "9th"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "110"),
      "ts": 1565299176485000,
      "data": {
        "letter": "J",
        "extra": "tenth"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "111"),
      "ts": 1565299176485000,
      "data": {
        "letter": "K",
        "extra": 11
      }
    },
    {
      "ref": Ref(Collection("Letters"), "112"),
      "ts": 1565299176485000,
      "data": {
        "letter": "L",
        "extra": ""
      }
    },
    {
      "ref": Ref(Collection("Letters"), "113"),
      "ts": 1565299176485000,
      "data": {
        "letter": "M"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "114"),
      "ts": 1565299176485000,
      "data": {
        "letter": "N",
        "extra": "14th"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "115"),
      "ts": 1565299176485000,
      "data": {
        "letter": "O",
        "extra": "fifteenth"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "116"),
      "ts": 1565299176485000,
      "data": {
        "letter": "P",
        "extra": "16th"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "117"),
      "ts": 1565299176485000,
      "data": {
        "letter": "Q",
        "extra": "seventeenth"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "118"),
      "ts": 1565299176485000,
      "data": {
        "letter": "R",
        "extra": "18th"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "119"),
      "ts": 1565299176485000,
      "data": {
        "letter": "S",
        "extra": "19th"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "120"),
      "ts": 1565299176485000,
      "data": {
        "letter": "T",
        "extra": "20th"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "121"),
      "ts": 1565299176485000,
      "data": {
        "letter": "U",
        "extra": "21st"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "122"),
      "ts": 1565299176485000,
      "data": {
        "letter": "V",
        "extra": "22nd"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "123"),
      "ts": 1565299176485000,
      "data": {
        "letter": "W",
        "extra": "twenty-third"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "124"),
      "ts": 1565299176485000,
      "data": {
        "letter": "X",
        "extra": 24
      }
    },
    {
      "ref": Ref(Collection("Letters"), "125"),
      "ts": 1565299176485000,
      "data": {
        "letter": "Y",
        "extra": "24 + 1"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "126"),
      "ts": 1565299176485000,
      "data": {
        "letter": "Z"
      }
    }
  ]
}

Note that the results are in sorted order. That’s because we provided specific ids for each Letters document rather than using FaunaDB’s auto-generated ids. When multiple documents are created in a single transaction, FaunaDB opportunistically runs portions of the transaction’s queries in parallel. This can result in seemingly random document ids that can appear to sort documents chaotically.

For example, let’s query the People documents to see their order. Copy the following query, paste it into the Shell, and run it:

Map(
  Paginate(Match(Index("all_people"))),
  Lambda("X", Get(Var("X")))
)

When you run the query, the output should be similar to:

{
  "data": [
    {
      "ref": Ref(Collection("People"), "240166254282801673"),
      "ts": 1565299238420000,
      "data": {
        "first": "Alan",
        "last": "Turing",
        "letter": "B"
      }
    },
    {
      "ref": Ref(Collection("People"), "240166254282802697"),
      "ts": 1565299238420000,
      "data": {
        "first": "Tim",
        "last": "Cook",
        "letter": "G"
      }
    },
    {
      "ref": Ref(Collection("People"), "240166254282803721"),
      "ts": 1565299238420000,
      "data": {
        "first": "Leslie",
        "last": "Lamport"
      }
    },
    {
      "ref": Ref(Collection("People"), "240166254282804745"),
      "ts": 1565299238420000,
      "data": {
        "first": "Marvin",
        "last": "Minsky",
        "letter": 1
      }
    },
    {
      "ref": Ref(Collection("People"), "240166254282805769"),
      "ts": 1565299238420000,
      "data": {
        "first": "Alan",
        "last": "Perlis",
        "letter": "A"
      }
    },
    {
      "ref": Ref(Collection("People"), "240166254282806793"),
      "ts": 1565299238420000,
      "data": {
        "first": "Grace",
        "last": "Hopper",
        "letter": "C"
      }
    },
    {
      "ref": Ref(Collection("People"), "240166254282807817"),
      "ts": 1565299238420000,
      "data": {
        "first": "Stephen",
        "last": "Cook",
        "letter": "F"
      }
    }
  ]
}

Follow the sort tutorial to see how to return the documents in order.

All documents, for large collections

The Paginate function defaults to returning up to 64 documents in a "page", which is a subset of the results.

Paginate works this way to prevent a common problem encountered with SQL databases when a user executes a query such as SELECT * FROM TABLE x;: the user doesn’t know how large each record might be, nor how many records might exist. This could result in fetching exceedingly large records, exceedingly large numbers of records, or both. These scenarios tax the database server, and the client code, consuming resources to store, transmit, and report results, and can often result in database server outages and/or client non-responsiveness.

So, what happens when you have more documents than Paginate would return? First, the default page size of 64 can be adjusted, up to the maximum value of 100,000 documents. If your collection has fewer documents than the maximum, you could return all of a collection’s documents in a single query. For collections with more than 100,000 documents, multiple queries are required.

We can simulate what happens by making Paginate's page size smaller by specifying the size parameter:

Map(
  Paginate(Match(Index("all_letters")), { size: 3 }),
  Lambda("X", Get(Var("X")))
)

Note that we’re using the collection index that we created in the Basic pagination section.

When you run this query, the results should be similar to:

{
  "after": [
    Ref(Collection("Letters"), "104")
  ],
  "data": [
    {
      "ref": Ref(Collection("Letters"), "101"),
      "ts": 1565299176485000,
      "data": {
        "letter": "A",
        "extra": "First"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "102"),
      "ts": 1565299176485000,
      "data": {
        "letter": "B",
        "extra": "second"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "103"),
      "ts": 1565299176485000,
      "data": {
        "letter": "C",
        "extra": "third"
      }
    }
  ]
}

These results show that our query returned the first 3 documents, in the same order as the previous results.

Notice the inclusion of the after field. This is a cursor, a marker the points to the entry that would start the next page of results. The cursor’s structure is determined by the values field of the index, which for our all_letters index is just each document’s Ref.

If we wanted to fetch the next page of results, we need to specify after in the Paginate function call:

Map(
  Paginate(
    Match(Index("all_letters")),
    {
      size: 3,
      after: [ Ref(Collection("Letters"), "104") ]
    }
  ),
  Lambda("X", Get(Var("X")))
)

When you run this query, the results should be similar to:

{
  "before": [
    Ref(Collection("Letters"), "104")
  ],
  "after": [
    Ref(Collection("Letters"), "107")
  ],
  "data": [
    {
      "ref": Ref(Collection("Letters"), "104"),
      "ts": 1565299176485000,
      "data": {
        "letter": "D",
        "extra": "4th"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "105"),
      "ts": 1565299176485000,
      "data": {
        "letter": "E",
        "extra": "fifth"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "106"),
      "ts": 1565299176485000,
      "data": {
        "letter": "F",
        "extra": "sixth"
      }
    }
  ]
}

As you can see, we now have the next group of 3 documents from our document collection.

Notice that, this time, there is now a before field in the results. Like after, before is a cursor that points to the first entry in the current page, so that FaunaDB can use the size parameter to determine which results to include in the previous page.

The size parameter can be different sizes in different queries. If we repeat the previous query with size adjust to 1:

Map(
  Paginate(
    Match(Index("all_letters")),
    {
      size: 1,
      after: [ Ref(Collection("Letters"), "104") ]
    }
  ),
  Lambda("X", Get(Var("X")))
)

the result would be similar to:

{
  "before": [
    Ref(Collection("Letters"), "104")
  ],
  "after": [
    Ref(Collection("Letters"), "105")
  ],
  "data": [
    {
      "ref": Ref(Collection("Letters"), "104"),
      "ts": 1565299176485000,
      "data": {
        "letter": "D",
        "extra": "4th"
      }
    }
  ]
}

So, now you might be wondering, "what happens when we run out of pages?". Let’s run the previous query again, but adjust the size parameter to 100 (far more than the number of People documents in our collection):

Map(
  Paginate(
    Match(Index("all_letters")),
    {
      size: 100,
      after: [ Ref(Collection("Letters"), "104") ]
    }
  ),
  Lambda("X", Get(Var("X")))
)

The result should be similar to:

{
  "before": [
    Ref(Collection("Letters"), "104")
  ],
  "data": [
    {
      "ref": Ref(Collection("Letters"), "104"),
      "ts": 1565299176485000,
      "data": {
        "letter": "D",
        "extra": "4th"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "105"),
      "ts": 1565299176485000,
      "data": {
        "letter": "E",
        "extra": "fifth"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "106"),
      "ts": 1565299176485000,
      "data": {
        "letter": "F",
        "extra": "sixth"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "107"),
      "ts": 1565299176485000,
      "data": {
        "letter": "G",
        "extra": "seventh"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "108"),
      "ts": 1565299176485000,
      "data": {
        "letter": "H",
        "extra": "eighth"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "109"),
      "ts": 1565299176485000,
      "data": {
        "letter": "I",
        "extra": "9th"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "110"),
      "ts": 1565299176485000,
      "data": {
        "letter": "J",
        "extra": "tenth"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "111"),
      "ts": 1565299176485000,
      "data": {
        "letter": "K",
        "extra": 11
      }
    },
    {
      "ref": Ref(Collection("Letters"), "112"),
      "ts": 1565299176485000,
      "data": {
        "letter": "L",
        "extra": ""
      }
    },
    {
      "ref": Ref(Collection("Letters"), "113"),
      "ts": 1565299176485000,
      "data": {
        "letter": "M"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "114"),
      "ts": 1565299176485000,
      "data": {
        "letter": "N",
        "extra": "14th"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "115"),
      "ts": 1565299176485000,
      "data": {
        "letter": "O",
        "extra": "fifteenth"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "116"),
      "ts": 1565299176485000,
      "data": {
        "letter": "P",
        "extra": "16th"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "117"),
      "ts": 1565299176485000,
      "data": {
        "letter": "Q",
        "extra": "seventeenth"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "118"),
      "ts": 1565299176485000,
      "data": {
        "letter": "R",
        "extra": "18th"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "119"),
      "ts": 1565299176485000,
      "data": {
        "letter": "S",
        "extra": "19th"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "120"),
      "ts": 1565299176485000,
      "data": {
        "letter": "T",
        "extra": "20th"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "121"),
      "ts": 1565299176485000,
      "data": {
        "letter": "U",
        "extra": "21st"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "122"),
      "ts": 1565299176485000,
      "data": {
        "letter": "V",
        "extra": "22nd"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "123"),
      "ts": 1565299176485000,
      "data": {
        "letter": "W",
        "extra": "twenty-third"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "124"),
      "ts": 1565299176485000,
      "data": {
        "letter": "X",
        "extra": 24
      }
    },
    {
      "ref": Ref(Collection("Letters"), "125"),
      "ts": 1565299176485000,
      "data": {
        "letter": "Y",
        "extra": "24 + 1"
      }
    },
    {
      "ref": Ref(Collection("Letters"), "126"),
      "ts": 1565299176485000,
      "data": {
        "letter": "Z"
      }
    }
  ]
}

We have received all of the people after the first 3. Notice that the after field is no longer present. When after is not included, that means that there are no pages after these results. When before is not included, there are no pages before these results. That makes it easy for application code to determine when to stop issuing queries when fetching lots of records.

Paginating indexes with multiple values

Paginating with a collection index is straightforward. Collection indexes have no values specified, so the index stores a Ref for each document from the index’s source collection.

How would you handle pagination for an index with values specified? Let’s find out! Here, we create a new index for the People collection that specifies multiple fields in values. Copy the following query, paste it into the Shell, and run it:

CreateIndex({
  name: "all_people_all_fields",
  source: Collection("People"),
  values: [
    { field: ["data", "first"] },
    { field: ["data", "last"] },
    { field: ["data", "letter"] },
    { field: ["ref"] }
  ]
})

This index records all of the fields from our People documents as well as each document’s Ref.

Now, let’s see the results. Copy the following query, paste it into the Shell, and run it:

Paginate(Match(Index("all_people_all_fields")))

When you run the query, the result should be similar to:

{
  "data": [
    [
      "Alan",
      "Perlis",
      "A",
      Ref(Collection("People"), "240166254282805769")
    ],
    [
      "Alan",
      "Turing",
      "B",
      Ref(Collection("People"), "240166254282801673")
    ],
    [
      "Grace",
      "Hopper",
      "C",
      Ref(Collection("People"), "240166254282806793")
    ],
    [
      "Leslie",
      "Lamport",
      null,
      Ref(Collection("People"), "240166254282803721")
    ],
    [
      "Marvin",
      "Minsky",
      1,
      Ref(Collection("People"), "240166254282804745")
    ],
    [
      "Stephen",
      "Cook",
      "F",
      Ref(Collection("People"), "240166254282807817")
    ],
    [
      "Tim",
      "Cook",
      "G",
      Ref(Collection("People"), "240166254282802697")
    ]
  ]
}

That worked well. Simple. Easy. So, what’s the problem? Let’s try operating on each document, like we’ve done previously. Copy the following query, paste is into the Shell, and run it:

Map(
  Paginate(Match(Index("all_people_all_fields"))),
  Lambda("X", Get(Var("X")))
)

This query is also very similar to previous queries. So, it should work fine. When you run the query, the result should be similar to:

invalid argument
Identifier expected, Array provided.
position: ["map","expr","get"]

What went wrong? The "shape" of our index is different. Since the index contains all of the fields in each document, as well as the document’s Ref, the Lambda function needs to accept exactly the same variables as defined in the index’s values.

The fix is easy. Copy the following query, paste it into the shell, and run it:

Map(
  Paginate(Match(Index("all_people_all_fields"))),
  Lambda(
    ["X", "Y", "Z", "ref"],
    Get(Var("ref"))
  )
)

This query changes the Lambda function to accept the variables X, Y, Z, and ref. This matches the number of fields in the index. We’re not going to use the values from the document’s fields, so they can have generic variable names. But we are going to use the document’s Ref, so we give that variable the name ref so that we know what it contains.

When you run the query, the result should be similar to:

{
  "data": [
    {
      "ref": Ref(Collection("People"), "240166254282805769"),
      "ts": 1565299238420000,
      "data": {
        "first": "Alan",
        "last": "Perlis",
        "letter": "A"
      }
    },
    {
      "ref": Ref(Collection("People"), "240166254282801673"),
      "ts": 1565299238420000,
      "data": {
        "first": "Alan",
        "last": "Turing",
        "letter": "B"
      }
    },
    {
      "ref": Ref(Collection("People"), "240166254282806793"),
      "ts": 1565299238420000,
      "data": {
        "first": "Grace",
        "last": "Hopper",
        "letter": "C"
      }
    },
    {
      "ref": Ref(Collection("People"), "240166254282803721"),
      "ts": 1565299238420000,
      "data": {
        "first": "Leslie",
        "last": "Lamport"
      }
    },
    {
      "ref": Ref(Collection("People"), "240166254282804745"),
      "ts": 1565299238420000,
      "data": {
        "first": "Marvin",
        "last": "Minsky",
        "letter": 1
      }
    },
    {
      "ref": Ref(Collection("People"), "240166254282807817"),
      "ts": 1565299238420000,
      "data": {
        "first": "Stephen",
        "last": "Cook",
        "letter": "F"
      }
    },
    {
      "ref": Ref(Collection("People"), "240166254282802697"),
      "ts": 1565299238420000,
      "data": {
        "first": "Tim",
        "last": "Cook",
        "letter": "G"
      }
    }
  ]
}

Always remember: Lambda functions must accept the same number of variables, and in the same order, as the "tuples" (or items) in the set being processed (our index represents the set of documents in the source collection).

Conclusion

This tutorial has demonstrated several different scenarios for paginating query results, how to access following pages, and how to deal with indexes with multiple fields defined in values. These strategies should help you query your documents in collections large and small!

Next steps

Was this article helpful?

We're sorry to hear that.
Tell us how we can improve! documentation@fauna.com

Thank you for your feedback!