15

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?

Improve this question

3 Answers 3

Reset to default
28

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Yeah, the first one looks like most common solution (according to what I googled before), but it seems that it doesn't really scalable. Imagine you have pagination - 20results/query(Q1) and there's a change that none of those 20 will match local filter(Q2), so you'll need to repeat Q1 n-times until you'll match Q2 20 times. And if you have 100k documents there's a chance to make 100k/20 requests, which is not about performance.
Understanding those performance implications should give you a pretty good idea of why Firestore doesn't support these types of queries. To do queries at scale with the performance guarantees that Firestore makes (taking 20 results takes a fixed amount of time, no many how many documents there are), means that it only allows queries where it can return all results from a single range.
Yeah, I just want to be 100% sure that I'm not missing any good workaround :)
if we have more then 15 fields then how can i manage? i need to give you example but where i put that code? @FrankvanPuffelen
@FrankvanPuffelen I'm just switching from RTD to Firestore and I'm still grasping the differences..in RTD I hit the same wall of filtering ranges on two fields so I ended up using two different queries.. I see that Firestore is no different so my situation doesn't change but for the problem in the question a query like this db.collection('products').orderBy('price').startAt('70000').endAt('90000').where('year', '>=', '2015').where('year', '<=', '2018') wouldn't work? Ordering by price limiting the query to desired prices plus filtering on another parameter range seems allowed right? Thanks
|
1

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.

Improve this answer

Comments

-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);

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.

Improve this answer

1 Comment

It doesn't matter if it is Flutter or not but you can't combine inequality operators on two different fields, OP also has a year field but your answer doesn't.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.