Firestore one range query with order by on different field

Question

I have the following Collection / Documents

Location Collection
{
  "geoHash" : "wwscjrm3nu01",
  "timeStamp" : "March 25, 2020 at 1:07:38 PM UTC-4" 
}
{
  "geoHash" : "wwscjrm2wc2h",
  "timeStamp" : "March 25, 2020 at 2:07:38 PM UTC-4" 
}

please replace the GeoHashs# with the example above

screenshot

So, I was trying to query a range of those GeoHashs with ordering them by timestamp "DESC", Like the following

    db.collection('location')
        .where('geoHash', '>=', 'wwkxt4kxmmvk')
        .where('geoHash', '<=', 'wwt4vsn22peq')
        .orderBy('timeStamp', 'DESC')
        .limit(15).get()

It didn't work, please the error below

Error getting documents { Error: 3 INVALID_ARGUMENT: inequality filter property and first sort order must be the same: geoHash and timeStamp
at callErrorFromStatus (/srv/node_modules/@grpc/grpc-js/build/src/call.js:30:26)
at Http2CallStream.call.on (/srv/node_modules/@grpc/grpc-js/build/src/call.js:79:34)
at emitOne (events.js:121:20)
at Http2CallStream.emit (events.js:211:7)
at process.nextTick (/srv/node_modules/@grpc/grpc-js/build/src/call-stream.js:97:22)
at _combinedTickCallback (internal/process/next_tick.js:132:7)
at process._tickDomainCallback (internal/process/next_tick.js:219:9)
code: 3,
details: 'inequality filter property and first sort order must be the 
same: geoHash and timeStamp',
metadata: Metadata { internalRepr: Map {}, options: {} } }

What if I have thousands or millions of documents in this collection per month or day!? Does anyone of you face this problem and what's the best way to work around it?

Update 0.2

I really appreciate your help and support in solving this issue. I have reproduced what I see in my end.

Currently, I am using the following query with Node js

       db.collection('locations')
        .where('geoHash', '>=', 'wwkxt4kxmmvk')
        .where('geoHash', '<=', 'wwt4vsn22peq')
        .orderBy('geoHash')
        .orderBy('timeStamp', 'desc')
        .limit(15).get().then(snapshot => {
          if (snapshot.empty) {
            console.log('No matching documents.');
          }
          snapshot.forEach(doc => {
            console.log(doc.data());
          });
        });

The expectation is filtering the GeoHashs > Order the GeoHashs > Order it by timestamp. So, the end results suppose to ordered timestamp, but I see is the following,

10:08:24.901 AM
   test
    { geoHash: 'wwscjrm3nu01',
      timeStamp: Timestamp { _seconds: 1585227600, _nanoseconds: 0 },
      post: '3' }
10:08:24.900 AM
   test
    { geoHash: 'wwscjrm3nu01',
      timeStamp: Timestamp { _seconds: 1585317000, _nanoseconds: 0 },
      post: '1' }
10:08:24.900 AM
   test
    { geoHash: 'wwscjrm2wc2h',
      timeStamp: Timestamp { _seconds: 1585314000, _nanoseconds: 0 },
      post: '2' }

As you see in the output above, I am excepting to see Post 1 > 2 > 3 based on timeStamp, but what i see above is Post 2 > 1 > 3.

Both of your conditions seem to be on the same field, so it seems quite different from my answer that you linked. 1) What doesn't work about this query? 2) And can you show a screenshot of the document that is not working correctly? 3)And finally: can you reproduce with hard-coded values for `range.lower` and `range.upper` and edit your question to show those values? — Frank van Puffelen, Mar 26 '20 at 16:59
Thanks @FrankvanPuffelen , I have updated the post covering all points you raised up, — L.kifa, Mar 26 '20 at 17:29
At the Firestore side, it doesn't really matter how geohashes are calculated. Can you just reproduce with hard-coded geohash values, and then use those in the question? The goal here is to rule out as much of your code as causing problems as possible on your side, so that we have to consider less. — Frank van Puffelen, Mar 26 '20 at 17:48
For the error message: it sounds like you need to add an `orderBy("geoHash")` before the `where` clauses. Which also means you'll need to define a compound index, but there will be an error message with a direct link to help you with that. — Frank van Puffelen, Mar 26 '20 at 17:49
you mean like this `orderBy("geoHash").where('geoHash', '>=', range.lower) .where('geoHash', '<=', range.upper) .orderBy('timeStamp', 'DESC') .limit(15).get()` — L.kifa, Mar 26 '20 at 17:52
I tried the way above, I got output, But the results weren't ordered by timeStamp — L.kifa, Mar 26 '20 at 18:27
@FrankvanPuffelen , I updated the post again with real GeoHashs including the lower and upper values. — L.kifa, Mar 26 '20 at 18:38
`Both ways the results were not ordered by timeStamp` Please don't say what the results **weren't**, but show what the results **were**, and how you expected them. Note that they will primarily be ordered by `geoHash`, and only on `timeStamp` after that. This is by design, and is one of the reasons the API requires you to add the extra `orderBy()` - so that it's explicit that the sort order is `geoHash` first, and then on `timeStamp`. — Frank van Puffelen, Mar 27 '20 at 02:31
I have reproduced the results and the expectations in the post. — L.kifa, Mar 27 '20 at 14:20

score 2 · Accepted Answer · answered Mar 27 '20 at 15:05

2

So your working query is:

db.collection('locations')
  .orderBy("geoHash")
  .where('geoHash', '>=', range.lower)
  .where('geoHash', '<=', range.upper)
  .orderBy('timeStamp', 'DESC')
  .limit(15).get()

From what I see the results you get are ordered by geoHash, and only then by timeStamp. So: if two documents have the same geoHash value, they will be in order of their timeStamp value.

This is working as intended: when you do a range query on a field, you always first need to order on that field. And since you first order on that field, the results come back in the order of that field.

This is inherent about Firestore's query model, so it may help to know more about that.. It's performance guarantees mean that it must always be able to stream the results from the database once it's found the starting point for your query. Re-ordering an unknown number of results would make it impossible to always satisfy that performance guarantee, so is not a supported operation.

To get the results in order of their timestamps, you'll want to reorder them in your application code.

answered Mar 27 '20 at 15:05

Frank van Puffelen

565,676
79
828
807

great answer Frank! – Chris32 Mar 27 '20 at 15:15
Understood, Thank you, really a quick question here. What are you recommending by ordering them on the client-side, I guess is not valid for thousands or millions of documents, What is your recommendations to address this issue? I am thinking about using Cloud SQL "MySQL" integrated with functions/. – L.kifa Mar 27 '20 at 15:25
Since you retrieve only 15 documents, that shouldn't be a problem. In general: you should only request data from Firestore that your user is actually going to see, so millions of documents is an unlikely number. When I see a developer retrieving large numbers of documents, it's most often because they're doing client-side aggregations. While that is a valid option, it wasted user's bandwidth and your money (as you're reading many documents that you won't display). See https://stackoverflow.com/a/58614687 – Frank van Puffelen Mar 27 '20 at 15:33
You are right!, but I mean what if the user wants to see only today's post and ordered by time and so on, and the query I have does not guarantee the descending order across all different values of getHashs by today's post, it could bring posts from other dates. – L.kifa Mar 27 '20 at 15:44
Yes, that is correct. This is a complete separate question, but I recommend reading my explanation here: https://stackoverflow.com/questions/55329334/how-can-i-use-a-geoquery-to-sort-by-both-location-and-certain-value-android/55329416#55329416 (which is surprisingly lacking any upvotes) – Frank van Puffelen Mar 27 '20 at 16:44
Again, Thank you for much for your help. I am going to start to consider an alternate way only for this by looking at the Cloud SQL as I mentioned unless Google has another product that could help in this case. – L.kifa Mar 27 '20 at 17:35

score 1 · Answer 2 · answered Mar 27 '20 at 12:42

1

As you can see in the public documentation doing a range filter and first orderBy on different fields as you are trying to do it's invalid in Firestore.

Also if you try to do multiples order by keep in mind that you are going to arrange all the results by the first orderBy and then, if there are similar results this will be arranged by the second orderBy (in your case you will arrange all your results first by geoHash and then by geoHash this will be sorted by date, but only this similar entries)

answered Mar 27 '20 at 12:42

Chris32

4,716
2
18
30

Thank you, Chris, I have reproduced the results and the expectations in the post. – L.kifa Mar 27 '20 at 14:21
Ok, I will try to be more clear on my answer. By the documentation what you are trying to do **IS INVALID**. What you are doing with your new query is ordering all the geoHash and then, whenever you have 2 or more similar geoHash this couple will be ordered by date, not the whole array. – Chris32 Mar 27 '20 at 15:04
The actual output of your query is the expected for that particular query, why? because it ordered your geoHash as `wwscjrm3nu01 > wwscjrm3nu01 > wwscjrm2wc2h` and then since you have 2 times `wwscjrm3nu01` it ordered just the timestamps for this geohash as `1585317000 > 1585227600`. This second orderBy won’t reorder the others geoHash. If you want all your results ordered by date then what you have to do is select your results by date instead of by geoHash – Chris32 Mar 27 '20 at 15:04
I fully understood the situation here. Thank you so much for your explanation. – L.kifa Mar 27 '20 at 15:19

Firestore one range query with order by on different field

2 Answers2

Linked