18

I want to query my Workout Collection for the latest workout from a routine. Meaning I query with whereEqualTo my routineKey, order it by the Started TimeStamp in descending order and then limit to 1 and then take the this 1st Key/Id of the Workout.

However this does not work. whereEqualTo and orderBy work separately but not combined. What am I doing wrong?

fm.getColRefWorkout().whereEqualTo("routineKey", routineKey).orderBy("startTimeStamp", Query.Direction.DESCENDING).limit(1).get().addOnSuccessListener(new OnSuccessListener<QuerySnapshot>() { 
                @Override
                public void onSuccess(QuerySnapshot documentSnapshots) {
                    workoutKey = documentSnapshots.getDocuments().get(0).getId();
                    //To stuff with this workoutKey
                }
            });
Alex Mamo
  • 130,605
  • 17
  • 163
  • 193
Jonas
  • 7,089
  • 15
  • 49
  • 110

1 Answers1

43

This query will not work unless you create an index for it. This can be done, by creating it manually in your Firebase Console or if you are using Android Studio, you'll find in your logcat a message that sounds like this:

FAILED_PRECONDITION: The query requires an index. You can create it here: ...

You can simply click on that link or copy and paste the URL into a web browser and your index will be created automatically.

Alex Mamo
  • 130,605
  • 17
  • 163
  • 193
  • Thanks for the answer! Haven't got a LogCat message but I created an index manually it worked. – Jonas May 15 '18 at 14:55
  • FirebaseFirestore.instance .collection('messages') .where('encSenderUId', isEqualTo: _loggedInUserId) .orderBy('sentOn', descending: false) .snapshots(), I want to do orderBy by `SentOn` field, not by encSenderUId then why firebase console says to encSenderUId to index by providing link. Its weird. Kindly suggest. Thanks. – Kamlesh Jun 16 '21 at 07:08
  • @Kamlesh Without seeing the entire code, I can't be much of a help. So please post a new question using its own [MCVE](https://stackoverflow.com/help/mcve), so I and other Firebase developers can help you. – Alex Mamo Jun 16 '21 at 07:10
  • 1
    I have created index for the same and it worked. It was odd but I did to solve this issue. Thanks. – Kamlesh Jun 16 '21 at 07:16
  • I broke my head over this issue - documentation says where and orderBy can't be used on different fields, whereas the answers above suggest that compound index creation would suffice. I am trying to do this and it doesn't work collection('users').where('uid', isNotEqualTo: user.uid).orderBy('accountCreationDate', descending: false) – bekon Apr 20 '22 at 09:29
  • @bekon Without seeing your index, it is hard to tell why it isn't working the way you expect. So please post a new question, here on StackOverflow, using its own [MCVE](https://stackoverflow.com/help/mcve), so I and other Firebase developers can help you. – Alex Mamo Apr 20 '22 at 10:07
  • thanks, just posted my question here - https://stackoverflow.com/questions/71938862/combining-where-and-orderby-clauses-in-firestore-flutter – bekon Apr 20 '22 at 11:30