How to query one field then order by another one in Firebase cloud Firestore?

Question

I'm struggling to make a (not so) complex query in firebase cloud firestore.

I simply need to get all docs where the id field == a given id.

Then order the results by their date and limit to 10 results.

So this is my current situation

db.firestore().collection('comments')
        .where("postId",'==',idOfThePost)
        .orderBy('date','asc').limit(10).get().then( snapshot => {
           //Nothing happens and the request wasn't even been executed
         })

I can get the result only if i don't use the orderBy query but i have to process this sorting for the needs of my application.

Someone has an idea to help me to fix this ?

thanks

Solution 1

You can do this by creating an Index in the firestore.

The first field of the index should be the equality field and the second field of the index should be the order by field.

Given your problem, you would need the following index:

first field: postId, 'asc'
second field: date, 'asc'

Solution 2

Please check the doc. It says

However, if you have a filter with a range comparison (<, <=, >, >=), your first ordering must be on the same field

you can try this code

db.firestore().collection('comments')
    .where("postId",'==',idOfThePost)
    .orderBy('postId')
    .orderBy('date','asc').limit(10).get().then( snapshot => {
       .....
     })

Solution 3

My Workaround

If you're googling this you've probably realized it can't be done traditionally. Depending on your problem though there may be some viable workarounds, I just finished creating this one.

Scenario

We have an app that has posts that appear in a feed (kind of like Reddit), each post has an algorithmic score 'score' and we needed a way to get the 'top posts' from 12-24 hours ago. Trying to query sorted by 'score' where timestamp uses > and < to build the 12-24 hour ago range fails since Firebase doesn't allow multiple conditional querying or single conditional querying with an descending sort on another field.

Solution

What we ended up doing is using a second field that was an array since you can compound queries for array-contains and descending. At the time a post was made we knew the current hour, suppose it was hour 10000 since the server epoch (i.e. floor(serverTime/60.0/60.0)). We would create an array called arrayOfHoursWhenPostIsTwelveToTwentyFourHoursOld and in that array we would populate the following values:

int hourOffset = 12;
while (hourOffset <= 24) {
    [arrayOfHoursWhenPostIsTwelveToTwentyFourHoursOld addObject:@(currentHour+hourOffset)];
    hourOffset++;
}

Then, when making the post we would store that array under the field hoursWhenPostIsTwelveToTwentyFourHoursOld

THEN, if it had been, say, 13 hours since the post was made (the post was made at hour 10000) then the current hour would be 10013, so we could use the array-contains query to see if our array contained the value 10013 while also sorting by algorithm score at the same time

Like so:

FIRFirestore *firestore = [Server sharedFirestore];
FIRCollectionReference *collection = [firestore collectionWithPath:@"Posts"];
FIRQuery *query = [collection queryOrderedByField:@"postsAlgorithmScore" descending:YES];
query = [query queryWhereField:@"hoursWhenPostIsTwelveToTwentyFourHoursOld" arrayContains:@(currentHour)];
query = [query queryLimitedTo:numberToLoad];

Almost Done

The above code will not run properly at first since it is using a compound index query, so we had to create a compound index query in firebase, the easiest way to do this is just run the query then look at the error in the logs and firebase SDK will generate a link for you that you can navigate to and it will auto-generate the compound index for your database for you, otherwise you can navigate to firebase>database>index>compound>new and build it yourself using hoursWhenTwelveToTwentyFourHoursOld: Arrays, score: Descending

Enjoy!

Solution 4

same here, it is weird why can't. below is another sample. can't get the result. Hoping firebase can reply about this and update the document.

dbFireStore.collection('room').where('user_id.'+global.obj_user.user_id,'==',true).orderBy('last_update').get().then((qs)=>{
  console.log(qs);
});

using other work-around solution is javascript array and array.sort()

Solution 5

I ran into the same issue yesterday on Android. The Callback was just not called. Today I suddenly got an error message. FAILED_PRECONDITION: The query requires an index. It even contains a URL in the error message to generate that index with one click.

It seems that if you want to use orderBy on your data, you need to create an index for that field. The index also needs to be in the correct order (DESC, ASC).

View more solutions