Workaround for "Range filters on different fields" limitation?

Question

newbie to firestore here and struggling with how to overcome the range filter on different fields limitation.

Use case is storage and retrieval of event/event data for a calendaring solution.

Calendar views can be different weeks, months etc, i,e each specific view has a defined start and end timestamp.

Stored events has a start timestamp and an end timestamp.

I need to query for all events that start, ends within or spans the views start and end timestamp.

Implementing this with a sql backend is trivial, but with firestore the range filter on different fields seems to make this impossible? Any ideas how this could be solved? Or if there as plans for supporting this kind of functionality in firestore?

BR Stefan

Answer 1

When I first encountered this problem it helped to first understand why this limitation exists. Yes, this may be trivial in a SQL database, but a SQL database will not scale like Firestore will. Firestore is likely backed by Google Big Table which, because of the way that indexing works, will not scale when a range filter is used on different properties. Essentially, Firestore isn't going to let you do something that will come to haunt you when your app scales.

Best option that I can see for you would be to do two queries and then combine the results in a set. Here's some sudo code:

let eventsStartingIn2017 = calendar_events.where("start_date", ">=", "1/1/2017").where("start_date", "<=", "31/12/2017")
let eventsEndingIn2017 = calendar_events.where("end_date", ">=", "1/1/2017").where("end_date", "<=", "31/12/2017")

let 2017Events = new Set();
2017Events.add(eventsStartingIn2017);
2017Events.add(eventsEndingIn2017);