How to query data between two dates range using Cloud FireStore?

user_7590031 0
Viewed 1522

I am trying to get a list of docs between two dates, but it is Invalid: Range filters on different fields

Here is my code:

await activationFBInstance
        .document('8thoOkTGYzVcntOxT2Av')
        .collection('posts')
        .where("startDate", isGreaterThan: Timestamp.fromDate(DateTime.now())).where("endDate", isLessThan: Timestamp.fromDate(DateTime.now()))
        .getDocuments()
        .then((value) {
      
      print(value.documents);
    }).catchError((onError) => print('error test 1 ' + onError.toString()));
google-cloud-firestore firebase dart flutter
2 Answers

The error message is pretty clear: you can only perform a range filter on a single field in Firestore.

The documentation on queries also has a good section on its limitations, which says:

In a compound query, range (<, <=, >, >=) and not equals (!=) comparisons must all filter on the same field.

So what you want isn't possible with Firestore at the moment. You'll either have to filter on one field in the query and the other one in your application code, come up with a different data model that gets you a closer match to the use-case with Firestore's query capabilities, or consider using a database that better supports your needs.

user_2091030

I also encountered the same problem when I was building an app. I wanted to get users between the range of provided dates (inclusive). For Example : Get users between 1 Jan 2020 to 1 May 2020 So what I did was the following.

I calculated a Date Hash (Integer value) from the given date and stored it inside users database. And when I was performing a query , I would get the hash value and compared it with the given range of dates.

In Layman terms all I did was map a Date to a HashValue in ascending order and stored it and when getting the value from the range of dates I calculated the HashValue from both the dates and compared them.

Code For Hashing

int calculateDateHash(DateTime date) {
final int day = date.day;
final int month = date.month;
final int year = date.year;
final int initialYear = 2021;
final int lastCalculated = 403;
final int constantMultiplier = 31;

int value = month * constantMultiplier;

value = day + value;

value = value + (403 * (year - initialYear));

return value;
}

While storing a date in Firebase first calculate the Hash by supplying the date you want to store and store that hash value inside firestore.

addUser(DateTime date) {
  FirebaseFirestore.instance
  .collection('yourCollection')
  .doc('yourDocument')
  .collection('yourCollection')
  .doc('yourDocument')
  .set({"dateHash": calculateDateHash(date)});
}

Code For Performing Query

Future<QuerySnapshot> getUsersFromMaster(
DateTime fromDate,
DateTime toDate) async {
var ref = FirebaseFirestore.instance.collection(yourCollection).doc(yourDoc).collection(yourCollection);

if (toDate == fromDate) {
  return await ref
      .where('dateHash', isEqualTo: calculateDateHash(fromDate))
      .get();
}

return await ref
    .where('dateHash', isLessThanOrEqualTo: calculateDateHash(toDate))
    .where('dateHash', isGreaterThanOrEqualTo: calculateDateHash(fromDate))
    .get();
}
user_127713600
Related