CODEWITHSUNDEEP
javascriptfirebasedartgoogle-cloud-firestore
L.kifa
L.kifa

Reputation: 95

Firestore one range query with order by on different field

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.

Upvotes: 3

Views: 1609

Answers (2)

Frank van Puffelen
Frank van Puffelen

Reputation: 600126

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.

Upvotes: 2

Chris32
Chris32

Reputation: 4961

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)

Upvotes: 1

Related Questions