Query documents with Custom Action + multiple filters with and without inequalities

Hey everyone,

I wanted to share a workaround I discovered for an issue that some of you might have encountered when dealing with filters in Firestore. In the past, we couldn't use multiple range filters in a query, which was a known limitation in Firestore. The good news is that this limitation no longer exists! However, FlutterFlow hasn't added support for it just yet (I'm sure the dev team is working hard on it).

So, in the meantime, I came up with a custom action that lets you query documents with multiple filters and inequalities. Plus, it checks if a field is empty or null and skips that filter in the query—pretty handy, right?

You can pass in as many filters as you need, and it will return a list of documents that you can display however you like (in a listview, table, page state variable, etc.).

I hope this helps someone out there! If you have any questions or need help with the code, feel free to ask. I'm happy to assist!

Future<List<PalletsRecord>> queryPallet(
  DateTime? creationfrom,
  DateTime? creationto,
  String? codClient,
  String? palletNumber,
  DateTime? billedFrom,
  DateTime? billedTo,
  String? companyID,
) async {
  //Create a list where to put the results of the query
  List<PalletsRecord> palletResults = [];
  // Start building the Firestore query
  Query<Map<String, dynamic>> query =
      FirebaseFirestore.instance.collection('pallets');
  //For me this is important is a filter to get only pallets from one company
  query = query.where('companyID', isEqualTo: companyID);
  // Here we are applying filters conditionally based on user inputs, as you can see there are multiple inequalities and also you can add some logic into it depending on the user's input.
  if (palletNumber != null && palletNumber.isNotEmpty) {
    query = query.where('palletNumber', isEqualTo: palletNumber);
  }
  if ((creationto != null) && (creationfrom != null)) {
    query = query.where('creationDate', isGreaterThanOrEqualTo: creationfrom);
    query = query.where('creationDate', isLessThanOrEqualTo: creationto);
  }
  if ((creationto == null) && (creationfrom != null)) {
    query = query.where('creationDate', isGreaterThanOrEqualTo: creationfrom);
//Here you will find a custom function called endOfDay, basically this function what it does is that it returns the hour 23:59:59 of that particular date, this was made because in case user only set up one date instead of a range means the user wants to check only that particular day so i need to limit the dates to that only
    query = query.where('creationDate',
        isLessThanOrEqualTo: endOfDay(creationfrom));
  }
  if (codClient != null && codClient.isNotEmpty) {
    query = query.where('clientCode', isEqualTo: codClient);
  }
if ((billedTo != null) && (billedFrom != null)) {
    query = query.where('billedDate', isGreaterThanOrEqualTo: billedFrom);
    query = query.where('billedDate', isLessThanOrEqualTo: billedTo);
  }
  if ((billedTo == null) && (creationfrom != null)) {
    query = query.where('billedDate', isGreaterThanOrEqualTo: billedFrom);
    query = query.where('billedDate',
        isLessThanOrEqualTo: endOfDay(billedFrom));
  }
// Execute the query based on the filters added before
  QuerySnapshot<Map<String, dynamic>> querySnapshot = await query.get();

  for (var doc in querySnapshot.docs) {
    //For each document found we take them and we add them into the list
    PalletsRecord to = PalletsRecord.fromSnapshot(doc);
    palletResults.add(to);
  }
//We return the list of documents
  return palletResults;
}