How to perform multiple orderBy query in firestore?

Question

I want to perform orderyBy "commentedBy" field and "geoHash" field together. Since firestore doesn't allow multiple orderBy on not equals operator. Is there any way to perform this query?

I'm trying to show all nearby posts which is not already commented by current user.

My code is below:

query
.collection("posts")
.orderBy("commentedBy")  
.whereNotIn("commentedBy",listOf(FirebaseAuth.getInstance().currentUser!!.uid))
.whereEqualTo("disabled", false)
.whereEqualTo("expired", false)
.orderBy("geoHash")
.whereGreaterThanOrEqualTo("geoHash", boundingBox.minimumMatch)
.whereLessThanOrEqualTo("geoHash",boundingBox.maximumMatch)distanceForRadius)

Answer 1

The problem is not that multiple orderBy sortings are applied on a 'not equals' query. For example, this does work as a query:

db.collection("users")
        .whereNotIn("firstName", Arrays.asList("Foo", "Bar"))
        .orderBy("firstName")
        .orderBy("lastName")

The problem with your query is that it's combining range clauses (whereGreaterThanOrEqualTo, whereLessThanOrEqualTo) with an inequality clause (whereNotIn) on different fields (geohash, uID). This is not supported as shown in the query limitations:

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

So your query cannot be done as is. Something that you can try is shifting the filtering for "not the current user" from the query into a stream (the getDocuments() method supports Java streams):

//Query
ApiFuture<QuerySnapshot> query = db.collection("posts")
    .whereEqualTo("disabled", false)
    .whereEqualTo("expired", false)
    .whereLessThanOrEqualTo("geohash", <geohash value>)
    .whereGreaterThanOrEqualTo("geohash", <geohash value>)
    .orderBy("geohash")
    .get();

//Stream that filters fetched documents to exclude current users
List<QueryDocumentSnapshot> documents = querySnapshot.getDocuments().stream()
    .filter(doc -> !Arrays.asList(<uid values>).contains(doc.getString("commentedBy")))
    .collect(Collectors.toList());

This worked on my tests with simple placeholder data for user IDs and geohashes (and after creating the relevant index as directed by the terminal output).

Answer 2

Yes it is possible. After making your query, run your code and you will encounter an error, if you want with log or if you want, find that link in the build section and click it. Confirm it. After the build process is finished, run the application again.