With the limitations of Firebase Firestore, it is impossible to use the SQL methodology of storing intervals and querying those intervals as described here.
I can't find any solution or best practice for this. How have people been using Firebase Firestore to store calendar repeating patterns such that querying for events on a particulate date will return events whose recurrence falls on that date, accounting for no end date as a possibility.
My thoughts so far:
- Write-heavy solution: Storing recurrence dates - Store all possible recurrence dates in an array within the event document. To account for no end date - store only for the calendar year. When user queries for the second year and beyond, retrieve all no-end date events and write all recurrence for that year. This might potentially be good enough since it is highly unlikely that users query beyond the calendar year. Even if they did, it will only be done once for each new year the user queries.
- Read-heavy solution: Query all events where repeating end date >= queried date - This is a catch all approach which will rely on the client side application to do its magic to show only events matching the queried date. This is a simpler approach but with potentially much heavier reads than its write counter part. E.g. If there are 1000 non-ending repeating events, that's 1000 document reads each navigation. As oppose to reading and writing 1000 events on the off chance that user navigates beyond the calendar year in the previous solution.
- Syncing an offline intermediary SQLite datastore - this is such an overkill, but would make SQL querying possible. However, it would introduce a whole lot more issues with syncing write operations between local and firebase.
I'm leaning towards solution 1 for a short term solution. The longer term solution would be solution 3 because it is more feasible for users to be able to manage their events offline.
Edit 4th option: Take advantage of the default offline data persistence behaviour of Firestore. Query all user events real time with a listener to update the cache when there is an updated or new event. This query is app-wide. Any further queries are done via a call to the cache. This ensures minimal read on the actual database, relying more on the cache data and the power of client side computing to filter on those queried events.
