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

user_12067114 0
Viewed 226

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?

google-cloud-firestore firebase document-database nosql javascript
2 Answers

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'
user_120671140

You could add an array that contains the combination of your user's available dates and area codes:

availableInAreaCodesOnDates:
  ["12345_2019-10-20", "94110_2019-10-20"]

Then you can query this with with a single array-contains operator.

user_2091030
Related