How could I filter by date and location availability in firestore?

Question

In my Firebase project I need to query all users who are available on a certain date at a certain city and sort by some other criteria.

Since we can't use multiple "array-contains" operations in one query, I tried the well known "map-trick" in Firestore, but now that I added ordering to the query, that's no longer an option.

let usersQuery = firestore.collection('Users')
                        .where('availableInAreaCodes.12345', '==', true)
                        .where('availableOnDates.2019-10-20', '==', true)
                        .orderBy('level', 'asc');

Any Ideas?

Answer 1

Thank you @DougStevenson and @FrankvanPuffelen for your help.

I found a good trade-off between number of documents, denormalization and read/write usage.

Note: This solution may no be suited for your scenario.

I added a new subcollection 'Availability' to each user document and split the availability data into weeks of the year (old documents get deleted every week and a new one is added every week).

I added areaCodes as an array, every weekday as a field and a reduced set of user data to the documents.

I had to create a total of 44 composite indexes (11 fields * 4 ordering combinations). Firestore's max is 200 per database.

In my scenario it was worth it, since this query is a key feature of the app.

let usersQuery = firestore.collectionGroup('Availability')
                        .where('areaCodes', 'array-contains', 'DE-1234')
                        .where('weekYear', '==', '2019-W42'))
                        .where('monday', '==', 'available')
                        .orderBy('userPreview.level', 'asc'