How should I query the firestore data by datetime?

Question

In my firestore database, there is datetime table. For example :

date : 2022년 10월 3일 오전 3시 11분 22초 UTC+9
due_to :2022년 11월 2일 오전 3시 11분 22초 UTC+9

I want to load data that has later 'due_to' than DataTime.now(). So I made a code :

stream: FirebaseFirestore.instance.collection('topic')
  .where('category',isEqualTo: catName) 
  .where('due_to',isLessThanOrEqualTo:Timestamp.fromDate(DateTime.now()))
  .orderBy('vote', descending: true)
  .limit(num_topic_limit)
  .snapshots(),

But I saw an error saying,

The initial orderBy() field "[[FieldPath([vote]), true]][0][0]" has to be the same as the where() field parameter "FieldPath([due_to])" when an inequality operator is invoked.

I don't understand why 'vote' field and 'due_to' field is being compared here, since using multiple where() without datetime field occurred no error.

What should I do to accomplish my goal ?

score 1 · Answer 1 · answered Oct 11 '22 at 09:38

Firestore queries can only sort or filter range on a single field.Firestore only allows queries where it can meet its performance guarantees, and for ordering/range-filtering on multiple fields that is not (currently) possible.

The Firestore documentation on queries contains the examples of a valid and invalid query.

Valid: Range filter and orderBy on the same field
citiesRef.whereGreaterThan("population", 100000).orderBy("population");
Invalid: Range filter and first orderBy on different fields
citiesRef.whereGreaterThan("population", 100000).orderBy("country");

You can also check these stackoverflow threads link1, link2 & link3

score 1 · Accepted Answer · answered Oct 11 '22 at 13:53

The error message is a bit weird, but it's trying to tell you that you need to add orderBy('due_to') before you can have the where clause on that field.

So:

stream: FirebaseFirestore.instance.collection('topic')
  .where('category',isEqualTo: catName) 
  .orderBy('due_to')
  .where('due_to',isLessThanOrEqualTo:Timestamp.fromDate(DateTime.now()))
  .orderBy('vote', descending: true)
  .limit(num_topic_limit)
  .snapshots(),

This also immediately means that the results will be in the order of due_to and only then in order of vote if they have the same due_to value. If that is not how you want to show the results, you'll have to re-order them in your application code.

I recommend checking out the Get to know Cloud Firestore, to learn more about Firestore - specifically the episode on queries: How do queries work in Cloud Firestore?

How should I query the firestore data by datetime?

2 Answers2