13

I have a large Firestore collection with 10,000 documents.

I want to show these documents in a table by paging and filtering the results at 25 at a time.

My idea, to limit the "reads" (and therefore the costs), was to request only 25 documents at a time (using the 'limit' method), and to load the next 25 documents at the page change.

But there's a problem. In order to show the number of pages I have to know the total number of documents and I would be forced to query all the documents to find that number.

I could opt for an infinite scroll, but even in this case I would never know the total number of results that my filter has found.

Another option would be to request all documents at the beginning and then paging and filtering using the client.

so, what is the best way to show data in this type of situation by optimizing performance and costs?

Thanks!

Renaud Tarnec
  • 79,263
  • 10
  • 95
  • 121
Ali Fumagalli
  • 239
  • 1
  • 2
  • 10
  • You can also check **[this](https://stackoverflow.com/questions/48534676/get-collectionreference-count/48540276)** out. – Alex Mamo May 04 '20 at 07:03

5 Answers5

8

You will find in the Firestore documentation a page dedicated to Paginating data with query cursors.

I paste here the example which "combines query cursors with the limit() method".

var first = db.collection("cities")
        .orderBy("population")
        .limit(25);

return first.get().then(function (documentSnapshots) {
  // Get the last visible document
  var lastVisible = documentSnapshots.docs[documentSnapshots.docs.length-1];
  console.log("last", lastVisible);

  // Construct a new query starting at this document,
  // get the next 25 cities.
  var next = db.collection("cities")
          .orderBy("population")
          .startAfter(lastVisible)
          .limit(25);
});

If you opt for an infinite scroll, you can easily know if you have reached the end of the collection by looking at the value of documentSnapshots.size. If it is under 25 (the value used in the example), you know that you have reached the end of the collection.

If you want to show the total number of documents in the collection you should use an aggregation query.

Renaud Tarnec
  • 79,263
  • 10
  • 95
  • 121
  • the code that you've written is the same that i use but, in your example "first" return correctly 25 items out of the 10,000 total. the number of pages should be 10000/25 = 400, but how can I get the total (10,000) without letting Firebase count 10,000 "reads"? – Ali Fumagalli May 03 '20 at 11:54
  • You should use a distributed counter which holds the number of documents, as explained in this answer: https://stackoverflow.com/a/61250956/3371862 – Renaud Tarnec May 03 '20 at 12:38
  • If my table has some filters, should i track counters for all the combinations? For example: collection "Customers" with 10,000 items (i save counter for "total value"), then i filter for customers enabled (enabled = true)... following this schema i should save the total number of enabled customers... and what happens if i have to save the number of enabled customers who are called "john"? – Ali Fumagalli May 03 '20 at 15:29
  • Yes you should probably do. It may be cumbersome but better than querying all the documents each time.... – Renaud Tarnec May 03 '20 at 15:59
  • Perhaps the best solution would be to save data to a cloud sql table and use cloud function to filter / order / paginate. it's a good idea? – Ali Fumagalli May 05 '20 at 20:04
  • It is a possible approach indeed. Telling you if it a good idea or not is very difficult without knowing all the parameters. – Renaud Tarnec May 05 '20 at 20:06
  • I have about 100 customers and each of them has, on average, 50,000 products. the customer registry has little information (business name, vat number, category) and I can manage it on firestore. For each customer I have to upload his products, maybe I could save the products on cloud sql, creating a separate table for each customer's products. – Ali Fumagalli May 05 '20 at 21:03
  • @AliFumagalli, sorry I cannot give you a precise feedback. I have no idea about the average connection time between a Cloud Function and Cloud SQL. I would suggest you give a try. – Renaud Tarnec May 05 '20 at 21:13
2

Firestore does not provide a way to know how many results would be returned by a query without actually executing the query and reading each document. If you need a total count, you will have to somehow track that yourself in another document. There are plenty of suggestions on Stack Overflow about counting documents in collections.

However, the paging API itself will not help you. You need to track it on your own, which is just not very easy, especially for flexible queries that could have any number of filters.

Doug Stevenson
  • 297,357
  • 32
  • 422
  • 441
2

My guess is you would be using Mat-Paginator and the next button is disabled because you cannot specify the exact length? In that case or not, a simple workaround for this is to get (pageSize +1) documents each time from the Firestore sorted by a field (such as createdAt), so that after a new page is loaded, you will always have one document in the next page which will enable the "next" button on the paginator.

Dilanka Asiri
  • 117
  • 3
2

What worked best for me:

  • Create a simple query
  • Create a simple pagination query
  • Combine both (after validating each one works separately)

Simple Pagination Query

const queryHandler = query(
  db.collection('YOUR-COLLECTION-NAME'),
  orderBy('ORDER-FIELD-GOES-HERE'), 
  startAt(0), 
  limit(50)
)
const result = await getDocs(queryHandler)

which will return the first 50 results (ordered by your criteria)

Simple Query

const queryHandler = query(
  db.collection('YOUR-COLLECTION-NAME'),
  where('FIELD-NAME', 'OPERATOR', 'VALUE')
)
const result = await getDocs(queryHandler)

Note that the result object has both query field (with relevant query) and docs field (to populate actual data)

So... combining both will result with:

const queryHandler = query(
  db.collection('YOUR-COLLECTION-NAME'),
  where('FIELD-NAME', 'OPERATOR', 'VALUE'),
  orderBy('FIELD-NAME'), 
  startAt(0), 
  limit(50)
)
const result = await getDocs(queryHandler)

Please note that the field in the where clause and in orderBy must be the same one! Also, it is worth mentioning that you may be required to create an index (for some use cases) or that this operation will fail while using equality operators and so on.

My tip: inspect the error itself where you will find a detailed description describing why the operation failed and what should be done in order to fix it (see an example output using js client in image below)

response example

GabLeRoux
  • 16,715
  • 16
  • 63
  • 81
ymz
  • 6,602
  • 1
  • 20
  • 39
0

Firebase V9 functional approach. Don't forget to enable persistence so you won't get huge bills. Don't forget to use where() function if some documents have restrictions in rules. Firestore will throw error if even one document is not allowed to read by user. In case bellow documents has to have isPublic = true.

firebase.ts

function paginatedCollection(collectionPath: string, initDocumentsLimit: number, initQueryConstraint: QueryConstraint[]) {

    const data = vueRef<any[]>([]) // Vue 3 Ref<T> object You can change it to even simple array.
    let snapshot: QuerySnapshot<DocumentData>
    let firstDoc: QueryDocumentSnapshot<DocumentData>
    let unSubSnap: Unsubscribe
    let docsLimit: number = initDocumentsLimit
    let queryConst: QueryConstraint[] = initQueryConstraint

    const onPagination = (option?: "endBefore" | "startAfter" | "startAt") => {
        if (option && !snapshot) throw new Error("Your first onPagination invoked function has to have no arguments.")
        let que = queryConst
        option === "endBefore" ? que = [...que, limitToLast(docsLimit), endBefore(snapshot.docs[0])] : que = [...que, limit(docsLimit)]
        if (option === "startAfter") que = [...que, startAfter(snapshot.docs[snapshot.docs.length - 1])]
        if (option === "startAt") que = [...que, startAt(snapshot.docs[0])]
        const q = query(collection(db, collectionPath), ...que)
        const unSubscribtion = onSnapshot(q, snap => {
            if (!snap.empty && !option) { firstDoc = snap.docs[0] }
            if (option === "endBefore") {
                const firstDocInSnap = JSON.stringify(snap.docs[0])
                const firstSaved = JSON.stringify(firstDoc)
                if (firstDocInSnap === firstSaved || snap.empty || snap.docs.length < docsLimit) {
                    return onPagination()
                }
            }
            if (option === "startAfter" && snap.empty) {
                onPagination("startAt")
            }
            if (!snap.empty) {
                snapshot = snap
                data.value = []
                snap.forEach(docSnap => {
                    const doc = docSnap.data()
                    doc.id = docSnap.id
                    data.value = [...data.value, doc]  
                })
            }
        })
        if (unSubSnap) unSubSnap()
        unSubSnap = unSubscribtion
    }
    function setLimit(documentsLimit: number) {
        docsLimit = documentsLimit
    }
    function setQueryConstraint(queryConstraint: QueryConstraint[]) {
        queryConst = queryConstraint
    }
    function unSub() {
        if (unSubSnap) unSubSnap()
    }
    return { data, onPagination, unSub, setLimit, setQueryConstraint }
}

export { paginatedCollection }

How to use example in Vue 3 in TypeScript

const { data, onPagination, unSub } = paginatedCollection("posts", 8, [where("isPublic", "==", true), where("category", "==", "Blog"), orderBy("createdAt", "desc")])

onMounted(() => onPagination()) // Lifecycle function
onUnmounted(() => unSub()) // Lifecycle function

function next() {
    onPagination('startAfter')
    window.scrollTo({ top: 0, behavior: 'smooth' })
}
function prev() {
    onPagination('endBefore')
    window.scrollTo({ top: 0, behavior: 'smooth' })
}

You might have problem with knowing which document is last one for example to disable button.

Mises
  • 4,251
  • 2
  • 19
  • 32