Firestore compound query with <= & >=

Question

I need to query a firestore collection for entries where a start time (which is a number) is >= slot.start and and <= slot.end.

like this:

.collection('Entries', ref => ref
    .where('timeStart', '>=', slot.timeStart)
    .where('timeEnd', '<=', slot.timeEnd))

but I get this error:

Invalid query. All where filters with an inequality (<, <=, >, or >=) must be on the same field.

I thought this query would work with composite index but as the error says that does not work.

When I change the query to this:

.collection('Entries', ref => ref
    .where('timeStart', '==', slot.timeStart)
    .where('timeEnd', '<=', slot.timeEnd))

I can create an index for it. Furthermore when I change the 2nd '<=' to an '==' aswell I don't even need an index for it.

I didn't think my query is that advanced and I'm a little bit disappointed that firestore can't do this query. So what is the best solution for my problem? Is the next best thing to omit the 2nd query statement like this

.collection('Entries', ref => ref.where('timeStart', '>=', slot.timeStart))

And then iterate through the results and check if timeEnd <= entry.timeEnd "manually"? That seems like a really bad solution for me because I may need to load a lot of data then.

Answer 1

You're going to have to pick one field and query that one then filter out the other on the client side. Compound Queries documentation is firm on this one.

You can only perform range comparisons (<, <=, >, >=) on a single field, and you can include at most one array_contains clause in a compound query:

Answer 2

In case this can be helpful, I was receiving the same error from this Flutter code, because of media_state:

  static Query<Media> getMediaByPlaceDayQuery(Place place, DateTime day) {
    return mediaCollectionRef
        .where("place_ref", isEqualTo: place.ref)
        .where("media_state",
            isNotEqualTo: MediaState.unsaved)
        .where("capture_time_epoch",
            isGreaterThanOrEqualTo: day.startOfDay.millisecondsSinceEpoch)
        .where("capture_time_epoch",
            isLessThanOrEqualTo: day.endOfDay.millisecondsSinceEpoch)
        .orderBy('capture_time_epoch');
  }

I solved it with this workaround:

static Query<Media> getMediaByPlaceDayQuery(Place place, DateTime day) {
    return mediaCollectionRef
        .where("place_ref", isEqualTo: place.ref)
        .where("media_state",
            whereIn: MediaState.values
                .where((state) => state != MediaState.unsaved)
                .map((state) => state.name)
                .toList())
        .where("capture_time_epoch",
            isGreaterThanOrEqualTo: day.startOfDay.millisecondsSinceEpoch)
        .where("capture_time_epoch",
            isLessThanOrEqualTo: day.endOfDay.millisecondsSinceEpoch)
        .orderBy('capture_time_epoch');
  }

---

So basically I converted

isNotEqualTo -> X

into

whereIn -> [everything but X].

Answer 3

Perhaps it wasn't the case in 2018, but now you can create indexes that will allow queries with multiple wheres.

Firebase calls those "Composite indexes" https://firebase.google.com/docs/firestore/query-data/index-overview#composite_indexes

Find the "Indexes" tab in the Firebase admin, create what you need and profit.

This code is taken straight from the Firebase docs:

citiesRef.where("country", "==", "USA")
         .where("capital", "==", false)
         .where("state", "==", "CA")
         .where("population", "==", 860000)