Flutter Firebase complex queries

Question

I have a complex query to perform. I have added a composite index for this query in my Firebase project. This is the query.

FirebaseFirestore.instance
              .collection('HouseDetails')
              .where('housePrice', isGreaterThan: GV.price[0].ceil() - 1)
              .where('housePrice', isLessThan: GV.price[1].ceil() + 1)
              .where('houseArea', isGreaterThan: GV.area[0].ceil() - 1)
              .where('houseArea', isLessThan: GV.area[1].ceil() + 1)
              .where('bathrooms',
                  isEqualTo: int.parse(bathrooms.first).ceil() + 1)
              .snapshots();

which throws an error

I/flutter ( 9116): ══╡ EXCEPTION CAUGHT BY GESTURE ╞═══════════════════════════════════════════════════════════════════
I/flutter ( 9116): The following assertion was thrown while handling a gesture:
I/flutter ( 9116): All where filters with an inequality (<, <=, >, or >=) must be on the same field. But you have
I/flutter ( 9116): inequality filters on 'FieldPath([housePrice])' and 'FieldPath([houseArea])'.
I/flutter ( 9116): 'package:cloud_firestore/src/query.dart':
I/flutter ( 9116): Failed assertion: line 484 pos 18: 'hasInequality == field'

After reading the documentation I understood that I cannot filter out multiple field types at once. But I did not understand how to perform the queries which needs more than one field for comparison (housePrice and houseArea in my case).

How Do I perform these queries?

Answer 1

I know it's annoying (I've been there), but Firestore doesn't allow where filters with inequality on different fields as is stated in the documentation:

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

You have two workarounds:

1. Pick one field to use where filters, and perform the rest of filtering in client side. The following code performs filtering on housePrice along with bathrooms in server side and houseArea in client side:

FirebaseFirestore.instance
  .collection('houseDetails')
  .where('housePrice', isGreaterThan: GV.price[0].ceil() - 1)
  .where('housePrice', isLessThan: GV.price[1].ceil() + 1)
  .where('bathrooms', isEqualTo: int.parse(bathrooms.first).ceil() + 1)
  .snapshots()
  // Transform each query snapshot into new stream as list of document snapshots
  .map<List<QueryDocumentSnapshot<Map<String, dynamic>>>>(
  // Discard any document snapshots whose house area data is not in the range
  (querySnapshot) => querySnapshot.docs.where((snapshot) {
    // Try to parse house area as number
    // Note: I don't know house area is a double or int, so I used num for type safety
    final houseArea = num.tryParse(snapshot.data()["houseArea"]);

    // Make sure house area is not null
    if (houseArea != null) {
      // and make sure house area is in the range you specified
      if (houseArea > GV.area[0].ceil() - 1) {
        if (houseArea < GV.area[1].ceil() + 1) {
          // Return true to add the document snapshot to list
          return true;
        }
      }
    }

    // Return false to discard the document snapshot
    return false;
  }).toList(),
);

2. Change your data model considering duplicating your data which is a common case for Firestore. For example, you can store the house details whose price range is 200k-350k in a collection called houseDetails-200k-350k, and perform filtering on houseArea and bathrooms:

FirebaseFirestore.instance
  .collection('houseDetails-200k-350k')
  .where('houseArea', isGreaterThan: GV.area[0].ceil() - 1)
  .where('houseArea', isLessThan: GV.area[1].ceil() + 1)
  .where('bathrooms', isEqualTo: int.parse(bathrooms.first).ceil() + 1)
  .snapshots();

Answer 2

You have to choose one field, and then from the result, you can filter another fields.

For example,

ModelHouseDetails _requestHouseDetailsFromSnapshot(DocumentSnapshot snapshot) {
return ModelHouseDetails(
    houseId: snapshot.id,
    houseArea: snapshot['houseArea'],
    housePrice: snapshot['housePrice'],
    bathrooms: snapshot['bathrooms']);
}

Stream<List<HouseDetails>> get listHouseDetails {
return FirebaseFirestore.instance.collection('request-service')
      .where('housePrice', isGreaterThan: GV.price[0].ceil() - 1)
      .where('housePrice', isLessThan: GV.price[1].ceil() + 1)
      .snapshots()
      .map((snapshot) =>
      snapshot.docs.where((element) => element['houseArea'] >= GV.area[0].ceil() - 1 && element['houseArea'] <= GV.area[1].ceil() + 1 && element['bathrooms'] == int.parse(bathrooms.first).ceil() + 1).map(_requestHouseDetailsFromSnapshot).toList());
}