Issues with server side pagination of Firestore

[Adrien_G]

Hello all,

I am trying to fetch data (all users of my Firestore database) in my retool app.

As there are obviously too many entries, I want to set up a server side pagination (as explained here : Setup of paginated query for firestore - #9 by Chris-Thompson) but I can't fetch other users than the first ones.

get_all_users is the Firestore query and users_in_scope is the concerned table.

Here is the Firestore request:

Here is the pagination section of the concerned table:

Here are the results, the 1st page is correctly displayed:

but when I try to display the next one, this is the error I get:

Am I missing something?
Thank you for your help!

Adrien

[Kabirdas]

Hey @Adrien_G!

This may be an issue with trying to reference timestamps in Firebase (see this thread for more detail), I'm seeing similar errors when trying to use a timestamp as a cursor for pagination. Based on that thread it looks like the only solution might be to use raw queries which would also mean setting up custom pagination with something like this.

The following seems to work as a Firebase query that'll pull back paginated data:

const TIMESTAMP_KEY = "timestamp"
const COLLECTION_NAME = "myFirestoreCollection"

const data = []
const cursor = cursors.value[page.value - 2] || null
await db
  .firestore()
  .collection(COLLECTION_NAME)
  .orderBy(TIMESTAMP_KEY)
  .startAfter(firestore.Timestamp.fromDate(new Date(cursor)))
  .limit(pageSize.value)
  .get()
  .then((querySnapshot) => {
    querySnapshot.forEach((doc) => {
      const values = doc.data()
      data.push({
        ...values,
        [TIMESTAMP_KEY]: values[TIMESTAMP_KEY]._seconds * 1000,
      })
    })
  })
return data

You can see it working in an app if you import the attached JSON file and hook up your Firebase resource, then replace the values for TIMESTAMP_KEY and COLLECTION_NAME with "created_at" and the name of your collection respectively.

It's a doozy of a workaround I can let you know here if better handling for Firebase timestamps is included. Also very open to hearing others' approaches here!
firebase_pagination_on_timestamp (1).json

[Adrien_G]

Hey,

I will definitely dig this and see if it helps.

Thanks for your answer!

[Joe_dare]

Hey @Kabirdas !

I'm trying to implement what you have suggested. The methods have changed slightly but my code I'm using (based on your recommendation) is below. It works for the first fetch, on page load. But doesn't seem to do anything when pressing the 'next' arrows on the table. It doesn't call the pagination query again.

Please help!

const TIMESTAMP_KEY = "createdAt"

await db
  .firestore()
  .collection("users")
  .orderBy(TIMESTAMP_KEY, "desc")
  .startAfter(firestore.Timestamp.fromDate(new Date(cursor)))
  .limit(table1.pagination.pageSize || 27)
  .get()
  .then((querySnapshot) => {
    querySnapshot.forEach((doc) => {
      const values = doc.data();
      let createdAtValue = values[TIMESTAMP_KEY];

      createdAtValue = new Date(createdAtValue);
      
      data.push({
        ...values,
        [TIMESTAMP_KEY]: createdAtValue,
      });
    });
  });

return data;

[Tess]

Hi @Joe_dare,

Thanks for reaching out!

Do you have an event handler to call the query again on page change? Something like this:

[Paulo]

Hi @Joe_dare, it looks like "data" is not defined in the query you shared. I tried @Kabirdas' query on my end and I got it to work by making a small change to it.

Try this approach using the new Table Component, and it should work like a charm:

Add-ons:

1. "Pagination: Server-side."
2. Any other add-on you need.

Event handlers:

1. "Change page: queryWeAreWorkingOn.trigger()"

Here is the setup that works on my end:

Here is the query so you can just copy and try on your end:

const TIMESTAMP_KEY = "created_at"
const data = []

await db
  .firestore()
  .collection("users")
  .orderBy(TIMESTAMP_KEY, "desc")
  .limit(table1.pagination.pageSize || 27)
  .offset(table1.pagination.currentPage * 27)
  .get()
  .then((querySnapshot) => {
    querySnapshot.forEach((doc) => {
      const values = doc.data();
      let createdAtValue = values[TIMESTAMP_KEY];

      createdAtValue = new Date(createdAtValue);
      
      data.push({
        ...values,
        [TIMESTAMP_KEY]: createdAtValue,
      });
    });
  });

return data;

With this query and table setting, I am able to change pages and the table refreshes with the expected data.

Let us know if this worked or if you have any questions!

[zing]

This solution works perfectly but requires changing the line
let createdAtValue = values[TIMESTAMP_KEY];
to
let createdAtValue = values[TIMESTAMP_KEY].seconds * 1000;

Otherwise, getting dates like 1970-01-20T21:42:46.351Z.

Listing:

const TIMESTAMP_KEY = "created_at"
const data = []

await db
  .firestore()
  .collection("users")
  .orderBy(TIMESTAMP_KEY, "desc")
  .limit(table1.pagination.pageSize || 27)
  .offset(table1.pagination.currentPage * 27)
  .get()
  .then((querySnapshot) => {
    querySnapshot.forEach((doc) => {
      const values = doc.data();
      let createdAtValue = values[TIMESTAMP_KEY].seconds * 1000;

      createdAtValue = new Date(createdAtValue);
      
      data.push({
        ...values,
        [TIMESTAMP_KEY]: createdAtValue,
      });
    });
  });

return data;