Firestore query with inequality filters on multiple fields

user_15482419 0
Viewed 388

I am building a calendar on IOS using Swift, with a Firestore back-end.

I am retrieving all the events for each day in a month at one time.

Currently I can only display events that start on a particular day.

This is no good if an event starts on a Friday night and ends on a Monday morning (think, weekend on holiday).

so...

I need to get all the Event documents from Firestore where the day currently being populated with events sits between the timestamps of the start and end fields on the Firestore document.

The obvious first choice is something like this:

db.collection("Event")
            .whereField("user", isEqualTo: userID)
            .whereField("start", isLessThanOrEqualTo: thisDate)
            .whereField("end", isGreaterThanOrEqualTo: thisDate)
            .getDocuments()

but it turns out Firebase won't let me run 2 inequality filters on different fields [:(

The only other option I can think of is to get all the events that started before today, scan through each one and check if it ended before today, then display the rest.

But this doesn't seem very efficient, especially if you think about 5 years down the line when a user might have an event for every day of the year (that's A LOT of documents to check through in a short space of time).

Does anybody have any suggestions as to how I might do this?

Thanks :)

UPDATE

I forgot to add this in, It's a screenshot of how the database is structured :)

(note that day, month, and year are obsolete fields now that I have started using timestamps)

enter image description here

google-cloud-firestore firebase swift
2 Answers

Firestore has query limitations that allow inequality filters only on the same field:

In a compound query, range (<, <=, >, >=) and not equals (!=, not-in) comparisons must all filter on the same field.

Unfortunately, it doesn't look like something that could be changed on the Google side because there were certain reasons for implementing the current design, for instance, the query speed.

In this case, you will need to use the solution that you've came up with in your code.

user_151238940

One solution we have used (not great but works) is time selectors. You need to add 'time selectors' to your model e.g.

const event = {
  ...baseEvent,
  timeSelector: {
    isoDay: 'yyyy-mm-dd',
    isoWeek: 'kkkk-WW', // luxon format
    isoMonth: 'yyyy-mm'
  }   
}

then depending on how wide your time interval is you can use array 'in' operator

db.collection("Event")
 .where('timeSelector.isoWeek', 'in', ['1999-01', '1999-02'])

That will greatly reduce the amount of documents pulled client side, however an extra client side filtering will be required, and playing with time intervals is always fun

user_99980280
Related