Firestore - After orderby geoHash not able to sort documents based on another field

Question

Example: Get top rated restaurants around me based on rating count

(Assume 10k restaurants around the area, so have to use limit)

My Query in Java:

ref.orderBy("geoHash")
     .orderBy("count", Query.Direction.DESCENDING)
     .startAt(startHash)
     .endAt(endHash)
     .limit(limit) 

But it doesn't works as expected even for a single GeoQueryBound.

Sample Document :

item1 : {
geoHash : tsp1
count : 100
}
item2 : {
geoHash : tsp1
count : 200
}
item3 : {
geoHash : tsp2 
count : 300
}

Now if we run the above query with geo bounds as tsp0 - tsp3 with limit as 2

Expected Results : [item3, item2]

Actual Results : [item2, item1]

Is this a limitation in firestore? Is there any workaround for it.

Answer 1

The query first orders on geoHash and only then orders on count, so the count only affects the results for documents where the geohash is the same. Since item1 and item2 have the lowest geohash values, they occur first in the index and are correctly returned.

There is no effective way to do this query on the database, unless you find a way to merge the count into the geohash values in a way to puts them in the order you want in the index. While this may be possible, the more likely workaround is to only filter on geohash and then get the top results by count in your application code.