Firestore multiple range query

Let's say I have a collection of cars and I want to filter them by price range and by year range. I know that Firestore has strict limitations due performance reasons, so something like:

db.collection("products")
  .where('price','>=', 70000)
  .where('price','<=', 90000)
  .where('year','>=', 2015)
  .where('year','<=', 2018)

will throw an error:

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

So is there any other way to perform this kind of query without local data managing? Maybe some kind of indexing or tricky data organization?

Solution 1:

The error message and documentation are quite explicit on this: a Firestore query can only perform range filtering on a single field. Since you're trying to filter ranges on both price and year, that is not possible in a single Firestore query.

There are two common ways around this:

1. Perform filtering on one field in the query, and on the other field in your client-side code.
2. Combine the values of the two range into a single field in some way that allows your use-case with a single field. This is incredibly non-trivial, and the only successful example of such a combination that I know of is using geohashes to filter on latitude and longitude.

Given the difference in effort between these two, I'd recommend picking the first option.

A third option is to model your data differently, as to make it easier to implement your use-case. The most direct implementation of this would be to put all products from 2015-2018 into a single collection. Then you could query that collection with db.collection("products-2015-2018").where('price','>=', 70000).where('price','<=', 90000).

A more general alternative would be to store the products in a collection for each year, and then perform 4 queries to get the results you're looking for: one of each collection products-2015, products-2016, products-2017, and products-2018.

I recommend reading the document on compound queries and their limitations, and watching the video on Cloud Firestore queries.

Solution 2:

In Flutter You can do something like this,

final _queryList = await db.collection("products").where('price','>=', 70000).get();
final _docL1 = _querList.where('price','<=', 90000);

It will definitely work.