firebase firestore compound query

Question

UserTable
---------
Username
Age
Gender
countryA
countryB
CityA
CityB

country = Italty ,city = Jhb;

Want to achieve below by querying to firestore

if(country==countryA || country == countryB){
    
    if(city == CityA || city == CityB){
        //TODO
        return the document;
    }
}

Trying to achieve above by querying to firestore

CollectionReference colRef = fireStore.collection("UserTable");

colRef.whereEqualTo("countryA",country).get();
colRef.whereEqualTo("countryB",country).get();
   
colRef.whereEqualTo("CityA",city).get();
colRef.whereEqualTo("CityB",city).get();

What do i code next?

How do I combine queries to achieve a success results

score 0 · Accepted Answer · answered Sep 01 '21 at 08:22

If your document looks like this:

$user (document)
 |
 --- Username: "Kennedy"
 |
 --- Age: 20
 |
 --- Gender: "male"
 |
 --- Country: "USA"
 |
 --- City: "NY"

The follwing logic makes sense to be used:

if(country==countryA || country == countryB)

As it means that you want to perform an OR query. This can be achieved in Java up to ten elements using:

colRef.whereIn("Country", Arrays.asList("countryA", "countryB"));

However, there is a constraint. You cannot add another:

.whereIn("City", Arrays.asList("CityA", "CityA"));

Since you can use at most one whereIn clause per query. To solve this, you have to use the first query and filter the cities in the client.

Back to your initial queries:

colRef.whereEqualTo("countryA",country).get();
colRef.whereEqualTo("countryB",country).get();
   
colRef.whereEqualTo("CityA",city).get();
colRef.whereEqualTo("CityB",city).get();

I see that you are using different fields for "countryA" and "countryB", as well as for "CityA" and "CityB", ending in having a schema that looks like this:

$user (document)
 |
 --- Username: "Kennedy"
 |
 --- Age: 20
 |
 --- Gender: "male"
 |
 --- countryA: "A"
 |
 --- CityA: "A"
 |
 --- CountryB: "B"
 |
 --- CityB: "B"

This also means that those queries don't help you perform an OR operation. So most likely you should consider using the first database schema and perform a whereIn() query and filter the results in memory.

Thanks Alex, exactly I'm using schema 2 and I want user to have countryA & B fields in the collection similar to cities ,so is there anyway to approach since where queries can't apply. — Kennedy Mathinya, Sep 02 '21 at 10:55
No, using the second database schema it's not possible. Check also this [answer](https://stackoverflow.com/questions/56865294/nosql-model-structure/56869041#56869041) out. So the recommended way is to change the structure of the document as mentioned in the first solution. Ok? — Alex Mamo, Sep 02 '21 at 11:00

firebase firestore compound query

1 Answers1