Home > database >  Firebase Firestore - order collection by timestamp with where clause
Firebase Firestore - order collection by timestamp with where clause

Time:04-23

I am trying to sort a collection data by timestamp (date field's type is timestamp in Firetore) as below:

postsSnap = await _firebaseFirestore
        .collection(Paths.posts)
        .where('review', isNotEqualTo: '')
        .orderBy('review')
        .where('content', isEqualTo: contentRef)
        .orderBy('date', descending: true)
        .limit(limit)
        .get();

But when I print the docs date value, it looks like there are not ordered by date field. Results are as following:

flutter: 2020-08-25 13:45:01.552
flutter: 2021-03-30 09:19:52.138
flutter: 2020-06-27 00:18:44.615
flutter: 2020-07-01 04:57:53.645
flutter: 2021-03-25 14:06:30.692
flutter: 2020-05-28 11:49:07.180
flutter: 2020-05-30 22:02:54.650
flutter: 2021-06-08 02:41:28.650
flutter: 2020-05-31 08:49:35.456
flutter: 2020-05-31 06:38:25.894

The index is also created:

enter image description here

I wonder if I am missing something while using the where clause in queries.

CodePudding user response:

First save the timestamp field as a Unix timestamp and then sort it.

You can convert datetime to timestamp as

var timestamp =  DateTime.now().millisecondsSinceEpoch

And from timestamp to datetime as

var dateTime = DateTime.fromMillisecondsSinceEpoch(timestamp * 1000);

CodePudding user response:

When you call:

.orderBy('review')

It means that you order the results ascending according to the review field. When you add an additional call to:

.orderBy('date', descending: true)

It means that your order the above results by date only when two or more reviews are the same. So the best option that you have is to remove the first orderBy by call and do the first ordering on the client.

  • Related