Combining where and orderBy clauses in Firestore Flutter

Question

I am breaking my head over this problem for several days. The documentation says there is a limitation to use where with equality and orderBy clauses in a single query - https://firebase.google.com/docs/firestore/query-data/order-limit-data

My code is as follows:

FirebaseFirestore.instance
                .collection('users')
                .where('uid', isNotEqualTo: user.uid)
                .orderBy('accountCreationDate', descending: false)
                .snapshots()

I have also created a composite index according to other answers in SO

but I am getting this error

"The initial orderBy() field \"[[FieldPath([accountCreationDate]), false]][0][0]\" has to be the
same as the where() field parameter \"FieldPath([uid])\" when an inequality operator is invoked."
The relevant error-causing widget was:
  StreamBuilder<DocumentSnapshot<Map<String, dynamic>>>

Based on the suggestion made by others, the best way to achieve my goal is to sort at a front-end level. My issue is I don't really know how to tell the program that an object returned from the firestore snapshot is of type User. The code for user model is below

lass User implements Comparable {
  final String user_name;
  final String uid;
  final String email;
  final List followers;
  final List following;
  final String pictureUrl;
  final List bookmarks;
  final DateTime accountCreationDate;

  const User(
      {required this.email,
      required this.uid,
      required this.user_name,
      required this.pictureUrl,
      required this.followers,
      required this.following,
      required this.bookmarks,
      required this.accountCreationDate});

  Map<String, dynamic> toJson() => {
        'user_name': user_name,
        'uid': uid,
        'email': email,
        'pictureUrl': pictureUrl,
        'followers': followers,
        'following': following,
        'bookmarks': bookmarks,
        'accountCreationDate': accountCreationDate
      };

  static User fromSnap(DocumentSnapshot snap) {
    var snapshot = snap.data() as Map<String, dynamic>;
    return User(
        email: snapshot['email'],
        user_name: snapshot['user_name'],
        uid: snapshot['uid'],
        pictureUrl: snapshot['pictureUrl'],
        followers: snapshot['followers'],
        following: snapshot['following'],
        bookmarks: snapshot['bookmarks'],
        accountCreationDate: snapshot['accountCreationDate'].toDate());
  }

  @override
  int compareTo(other) {
    if (accountCreationDate.isAfter(other.accountCreationDate)) {
      return 1;
    } else {
      return -1;
    }
  }
}

This is what I am trying to do

body: StreamBuilder(
            stream: FirebaseFirestore.instance
                .collection('users')
                .where('uid', isNotEqualTo: user.uid)
                .snapshots(),
            builder: (context,
                AsyncSnapshot<QuerySnapshot<Map<String, dynamic>>> snapshot) {
              if (snapshot.connectionState == ConnectionState.waiting ||
                  !snapshot.hasData) {
                return const Center(
                  child: CircularProgressIndicator(),
                );
              }
              List sortedUsers = snapshot.data!.docs.map((a,b)=> a.compareTo(b))
...

score 1 · Answer 1 · answered Apr 20 '22 at 11:43

you need to adapt your index but I saw some sources that stated the following should work:

FirebaseFirestore.instance
   .collection('users')
   .where('uid', isNotEqualTo: user.uid)
   .orderBy("uid", descending:false)
   .orderBy('accountCreationDate', descending: false)
.snapshots()

The second solution would be to save the snapshot to a list and filter it there without using firebase sdk

Please see this source for more info on your firebase problem:

Trying to order Firestore results in query

Also for users coming across that here is the firebase documentation regarding using where and orderBy:

https://firebase.google.com/docs/firestore/query-data/order-limit-data#limitations

https://cloud.google.com/firestore/docs/query-data/queries?hl=de

thanks @ Enviro Apps, you and Dev suggested the same approach, but surprisingly it doesn't work - nothing is returned from the firestore. Also it doesn't really solve my issue of sorting by accountCreationDate field, but thanks anyways! — bekon, Apr 20 '22 at 12:06

Dev · Answer 2 · 2022-04-21T03:44:01.887

1

As per error the condition field should appear in order by also so Use

FirebaseFirestore.instance
                .collection('users')
                .where('uid', isNotEqualTo: user.uid)
                .orderBy('uid')
                .orderBy('accountCreationDate', descending: false)
                .snapshots()

Just see if it works.

Now the main part comes that its not ordered by accountCreationDate which you have to handle at client side. That is sort on flutter side using accountCreationDate field

At client side use (Also you don't need implements Comparable)

List sortedUsers = snapshot.data!.docs.map((a,b)=> a.compareTo(b))

 sortedUsers.sort((a,b)=> a.accountCreationDate.compareTo(b.accountCreationDate));

edited Apr 21 '22 at 03:44

answered Apr 20 '22 at 11:43

Dev

6,628
2
25
34

yeap, I think you're right - the only viable option is to sort on the client side. I am still a newbie with regards to Flutter. Can you suggest what is the right approach to do that? I've tried this snapshot.data!.docs..sort((a,b)=> a.compareTo(b)); but I don't get how a program should understand that a and b of a certain type and they support compareTo method. The type of snapshot is AsyncSnapshot>> – bekon Apr 20 '22 at 12:40
By your code I can't tell if you use a Model class or what version of Firestore but this is how a sort function could look like: users.sort((a,b) { var adate = a['accountCreationDate']; var bdate = b['accountCreationDate']; return adate.compareTo(bdate); }); – DEFL Apr 20 '22 at 12:54
updated the original post with the model class – bekon Apr 20 '22 at 13:29
check the updated solution for client part – Dev Apr 21 '22 at 03:45

Combining where and orderBy clauses in Firestore Flutter

2 Answers2

Linked