CODEWITHSUNDEEP
firebasenosqlgoogle-cloud-firestore
Dmytro Levchenko
Dmytro Levchenko

Reputation: 423

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?

Upvotes: 15

Views: 10190

Answers (3)

Frank van Puffelen
Frank van Puffelen

Reputation: 598765

Update: since late March 2024 Firestore can now have inequality and range conditions on multiple fields in a query. See the documentation here: https://firebase.google.com/docs/firestore/query-data/multiple-range-fields

New, up-to-date answer above 👆

Old, outdated answer below 👇

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.

Upvotes: 28

Mayur Agarwal
Mayur Agarwal

Reputation: 1814

In Flutter You can do something like this,

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

Add more queries as you want, but for firestore, you can only request a limited number of queries, and get the data. After that you can filter out according to your need.

Upvotes: -2

Taku
Taku

Reputation: 5928

You can't do multiple range queries as there are limitations mentioned here, but with a little cost to the UI, you can still achieve by indexing the year like this.

db.collection("products")
  .where('price','>=', 70000)
  .where('price','<=', 90000)
  .where('yearCategory','IN', ['new', 'old'])

Of course, new and old go out of date, so you can group the years into yearCategory like yr-2014-2017, yr-2017-2020 so on. The in can only take 10 elements per query so this may give you an idea of how wide of a range to index the years.

You can write to yearCategory during insert or, if you have a large range such as a number of likes, then you'd want another process that polls these data and updates the category.

Upvotes: 1

Related Questions