How to start Firestore query from a particular document number without using OFFSET?

Question

I have a Firestore collection named 'users' and has many documents by the name of each user.

I want to retrieve list of 25 users at a time in alphabetical order and this is what I tried:

const allUsersRef = admin.firestore().collection('users').orderBy('name').offset(0).limit(25)      
  allUsersRef.get().then((top25Users) => {
    let usersList = '``` << Users LIST >>\n'
    if (!top25Users.empty) {
      top25Users.forEach(eachUser => { 
        usersList = usersList + `\n${eachUser.data().name} \n${eachUser.data().id}`  
      })
      
      console.log(usersList) 
      return 
    } else {
      message.channel.send('Looks like we have no users at the moment!')
      return  
    }
  }).catch((error) => {
    console.log(error)
    return 
  })

This way I can get the top 25 users easily! But what if I want the next 25? This is a Discord Bot and not an Android Application where I can add a button [view more] and then continue the results query.start() as shown in this firebase video

I can use OFFSET but the number of users is large so using offset(500) won't be affordable :'(

Also I need to fetch users in alphabetical order and when new users register, the order changes.

TL,DR: If I had a list of my users in alphabetical order, how do I get users from 126th position to 150th position on the list which is sort of page 5 for my 25/page query! and without using offset because that just uses more resources!

I had this in firebase realtime database first but then I needed some more advanced querying so I have migrated here :)

Database Struture: Just a single collection named USERS and documents named as username in it.

PS:

const startAtRes = await db.collection('cities')
  .orderBy('population')
  .startAt(1000000)
  .get();

Using something like this ^ from Firebase Documentation is not possible because I won't be knowing from where to start from. As the list changes as new users Register!

If you know the last username that you've fetched, you could use `.startAt(username)` - https://firebase.google.com/docs/firestore/query-data/query-cursors#set_cursor_based_on_multiple_fields — danwillm, Jul 14 '20 at 16:14
@danwillm that is the only issue that I mentioned. I wont be knowing that :( Maybe I'll rely on realtime database for a couple of features now — Dharmaraj, Jul 14 '20 at 16:54
do you know what doc you finished at? what do you know about your collection in order to paginate it? — danwillm, Jul 15 '20 at 07:57
@danwillm no! Because when new users registers the list changes — Dharmaraj, Jul 15 '20 at 08:12
What is your use case? Every time the query is run it may return different results as new users sign up, but does it matter? You can just keep querying until you reach the end, or do you want to stop at a specific user? — danwillm, Jul 15 '20 at 09:44
@danwillm suppose there are 800 users arranged in alphabetical order. I want to fetched users at position from 500 to 525. I can use offset(500) but then it will cost me 525 reads which is not affordable — Dharmaraj, Jul 15 '20 at 10:05

score 1 · Accepted Answer · answered Jul 14 '20 at 16:11

1

Firestore does not support efficient offset based pagination. When you use offset(), you're paying for reads of all the documents up to that point. The only availabe efficient pagination requires that you provide an anchor document, or properties of the anchor document, to navigate between pages, as described in the documentation.

answered Jul 14 '20 at 16:11

Doug Stevenson

297,357
32
422
441

Thanks for that information. I had checked [this answer](https://stackoverflow.com/a/48454946/13130697) of yours after reading the Docs but asked the question to check if any workaround exists. Heading to Firebase Feature Request :) – Dharmaraj Jul 14 '20 at 16:56

How to start Firestore query from a particular document number without using OFFSET?

1 Answers1