Firestore query with where and orderBy returning null when using in Streams in Flutter App

Question

I am trying to get some document and sort them but when I am doing it with .orderBy it is returning null. If I use only .where and don't use orderBy, it is working fine.

I want to get some documents in a collection name 'expenses' sorted with date. But when I am doing it with .where and .orderBy it is returning null. If I use only .where and don't use orderBy, it is working fine.

Stream<List<Expense>> getExpenseStream() {
    UserModel user;

    return firestore
        .collection('expenses')
        .where("usersList", arrayContains: auth.currentUser?.phoneNumber)
        .snapshots()
        .map((event) {
      List<Expense> expenseList = [];
      for (var i in event.docs) {
        expenseList.add(Expense.fromMap(i.data()));
      }

      return expenseList;
    });
// It worked correctly

The above code works perfect without sorting.

Stream<List<Expense>> getExpenseStream() {
    UserModel user;

    return firestore
        .collection('expenses')
        .where("usersList", arrayContains: auth.currentUser?.phoneNumber)
        .orderBy("date", descending: true)
        .snapshots()
        .map((event) {
      List<Expense> expenseList = [];
      for (var i in event.docs) {
        expenseList.add(Expense.fromMap(i.data()));
      }

      return expenseList;
    });

This code is returning null.

And as I can see in my App the function fetched data for a second and then returned null.

Answer 1

If the problem starts when you add the orderBy instruction, it's likely because at that point Firestore needs a composite index to perform the query and (unlike single-field indexes) you have to create that explicitly yourself.

You might want to try catching and logging errors from the code accessing the database, as a missing index typically should give you an error message and that error message includes a link to the Firestore dashboard with all the necessary info for the index filled in already. For more on this, also see the documentation on creating a missing index through an error message.

Alternatively, you can create a new composite index with the necessary fields yourself. In that case you'll need an to first add the usersList field in ascending order, and then the date field in descending order. For more on this, also see the documentation on creating a missing index manually