How to search in Firestore using multiple fields of documents for android?

Question

I'm using a Firebase Firestore for android to store data. I tried to search data from documents.

My Firestore structure is:

Collection (Products) - Document (Auto Generated ID) - Field (NumberOfPeople,OfferStartDate,OfferEndDate,OfferPrice,Location)

I wrote query for search data on those fields.

CollectionReference collectionOfProducts = db.collection("Products");

collectionOfProducts
                    .whereEqualTo("Location", location)
                    .whereGreaterThanOrEqualTo("OfferPrice", offerPrice)
                    .whereLessThanOrEqualTo("OfferPrice", offerPrice)
                    .whereGreaterThanOrEqualTo("OfferStartDate", date)
                    .whereLessThanOrEqualTo("OfferEndDate", date)
                    .get()

I want search result like this: An offer which is between start date and end date, where offer price is greater than equal or less than equal on give price range. This query is not working in android studio.

How to do this in firestore firebase?

Answer 1

According to the official documentation regarding Cloud Firestore queries, please note that there some query limitations:

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

So a Query object that contains a call to both methods:

.whereGreaterThanOrEqualTo("OfferStartDate", date)
.whereLessThanOrEqualTo("OfferEndDate", date)

Is actually not possible, as "OfferStartDate" and "OfferEndDate" are different properties.

The best solution I can think of is to use only one of these method calls and do the other filtering on the client.

Another possible solution might be to use denormalization and duplicate the data in certain intervals. In this way, you'll always know the time periods and you'll be able to create the corresponding queries.

Answer 2

To the best of my knowledge, Firestore only lets you use where<Greater/Less>ThanOrEqualTo() and where<Greater/Less>Than() a single field and all other filter operations on other fields can only be whereEqualTo().

Some workarounds for your specific case include -

1) Modifying your query to

collectionOfProducts
                    .whereGreaterThanOrEqualTo("OfferStartDate", date)
                    .whereEqualTo("Location", location)
                    .get() 

And then performing the subsequent filtering on the result in your app code.

Alternately, you can perform your filter on "OfferPrice" and "Location" in your query and the remaining filters can be applied to the query result.

2) You can use firebase functions or other server code to write logic that performs customized filtering and fetch the result that way.